CREATE TABLE FileGroupField ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Name VARCHAR(200) NOT NULL UNIQUE, Description VARCHAR(MAX) NULL ) GO CREATE TABLE FileGroupFieldValue ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, FileGroupID INT NOT NULL REFERENCES FileGroup(ID), FileGroupFieldID INT NOT NULL REFERENCES FileGroupField(ID), Value VARCHAR(MAX) NULL ) GO CREATE NONCLUSTERED INDEX IX_FileGroupFieldValue_FileGroupID ON FileGroupFieldValue(FileGroupID ASC) GO CREATE NONCLUSTERED INDEX IX_FileGroupFieldValue_FileGroupFieldID ON FileGroupFieldValue(FileGroupFieldID ASC) GO ALTER TABLE Line ADD MaxFaultDistance FLOAT NULL GO ALTER TABLE Line ADD MinFaultDistance FLOAT NULL GO CREATE NONCLUSTERED INDEX IX_Channel_MeterID_MeasurementTypeID_MeasurementCharacteristicID_PhaseID_HarmonicGroup ON Channel(MeterID ASC, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) GO CREATE TABLE AssetGroup ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Name VARCHAR(200) NOT NULL ) GO EXEC sp_rename 'DeviceFilter', 'DeviceFilter_org' GO CREATE TABLE DeviceFilter ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, UserAccount VARCHAR(500) NOT NULL, Name NVARCHAR(500) NOT NULL, FilterExpression NVARCHAR(MAX) NOT NULL, AssetGroupID INT NOT NULL ) GO INSERT INTO DeviceFilter SELECT UserAccount, DeviceFilter_org.Name, FilterExpression, AssetGroup.ID FROM DeviceFilter_org JOIN MeterGroup ON DeviceFilter_org.MeterGroupID = MeterGroup.ID JOIN AssetGroup ON MeterGroup.Name = AssetGroup.Name OR (MeterGroup.Name = 'AllMeters' AND AssetGroup.Name = 'AllAssets') GO CREATE TABLE MeterAssetGroup ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, MeterID INT NOT NULL REFERENCES Meter(ID), AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID) ) GO CREATE NONCLUSTERED INDEX IX_MeterAssetGroup_MeterID ON MeterAssetGroup(MeterID ASC) GO CREATE NONCLUSTERED INDEX IX_MeterAssetGroup_AssetGroupID ON MeterAssetGroup(AssetGroupID ASC) GO CREATE TRIGGER Meter_AugmentAllAssetsGroup ON Meter AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO MeterAssetGroup(MeterID, AssetGroupID) SELECT Meter.ID, AssetGroup.ID FROM inserted Meter CROSS JOIN AssetGroup WHERE AssetGroup.Name = 'AllAssets' END GO CREATE TABLE LineAssetGroup ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, LineID INT NOT NULL REFERENCES Line(ID), AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID), ) GO CREATE NONCLUSTERED INDEX IX_LineAssetGroup_LineID ON LineAssetGroup(LineID ASC) GO CREATE NONCLUSTERED INDEX IX_LineAssetGroup_AssetGroupID ON LineAssetGroup(AssetGroupID ASC) GO CREATE TRIGGER Line_AugmentAllAssetsGroup ON Line AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO LineAssetGroup(LineID, AssetGroupID) SELECT Line.ID, AssetGroup.ID FROM inserted Line CROSS JOIN AssetGroup WHERE AssetGroup.Name = 'AllAssets' END GO CREATE TABLE AssetGroupAssetGroup ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, ParentAssetGroupID INT NOT NULL REFERENCES AssetGroup(ID), ChildAssetGroupID INT NOT NULL REFERENCES AssetGroup(ID), ) GO CREATE NONCLUSTERED INDEX IX_AssetGroupAssetGroup_ParentAssetGroupID ON AssetGroupAssetGroup(ParentAssetGroupID ASC) GO CREATE NONCLUSTERED INDEX IX_AssetGroupAssetGroup_ChildAssetGroupID ON AssetGroupAssetGroup(ChildAssetGroupID ASC) GO CREATE TABLE MaintenanceWindow ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, MeterID INT NOT NULL REFERENCES Meter(ID), StartTime DATETIME, EndTime DATETIME ) GO INSERT INTO AssetGroup(Name) VALUES('AllAssets') GO INSERT INTO AssetGroup(Name) SELECT Name FROM MeterGroup WHERE Name <> 'AllMeters' GO INSERT INTO MeterAssetGroup SELECT MeterMeterGroup.MeterID, AssetGroup.ID FROM MeterMeterGroup JOIN MeterGroup ON MeterMeterGroup.MeterGroupID = MeterGroup.ID JOIN AssetGroup ON MeterGroup.Name = AssetGroup.Name OR (MeterGroup.Name = 'AllMeters' AND AssetGroup.Name = 'AllAssets') GO ALTER TABLE UserAccount ADD CONSTRAINT UQ_UserAccount_Name UNIQUE(Name) GO ALTER TABLE UserAccount ADD PhoneConfirmed BIT NOT NULL DEFAULT 0 GO ALTER TABLE UserAccount ADD EmailConfirmed BIT NOT NULL DEFAULT 0 GO ALTER TABLE UserAccount ADD Approved BIT NOT NULL DEFAULT 0 GO UPDATE UserAccount SET EmailConfirmed = 1, Approved = 1 GO CREATE TABLE UserAccountAssetGroup ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, UserAccountID UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID), AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID), Dashboard BIT NOT NULL DEFAULT 1, Email BIT NOT NULL DEFAULT 0 ) GO INSERT INTO UserAccountAssetGroup SELECT UserAccountMeterGroup.UserAccountID, AssetGroup.ID, 1, 1 FROM UserAccountMeterGroup JOIN MeterGroup ON UserAccountMeterGroup.MeterGroupID = MeterGroup.ID JOIN AssetGroup ON MeterGroup.Name = AssetGroup.Name OR (MeterGroup.Name = 'AllMeters' AND AssetGroup.Name = 'AllAssets') GO CREATE TRIGGER UserAccount_AugmentAllAssetsGroup ON UserAccount AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO UserAccountAssetGroup(UserAccountID, AssetGroupID) SELECT UserAccount.ID, AssetGroup.ID FROM inserted UserAccount CROSS JOIN AssetGroup WHERE AssetGroup.Name = 'AllAssets' END GO ALTER TABLE XSLTemplate ADD CONSTRAINT UQ_XSLTemplate_Name UNIQUE(Name) GO ALTER TABLE EmailType ADD SMS BIT NOT NULL DEFAULT 0 GO CREATE TABLE EventEmailParameters ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, EmailTypeID INT NOT NULL UNIQUE REFERENCES EmailType(ID), TriggersEmailSQL VARCHAR(MAX) NOT NULL DEFAULT 'SELECT 0', EventDetailSQL VARCHAR(MAX) NOT NULL DEFAULT 'SELECT '''' FOR XML PATH(''EventDetail''), TYPE', MinDelay FLOAT NOT NULL DEFAULT 10, MaxDelay FLOAT NOT NULL DEFAULT 60 ) GO CREATE TABLE UserAccountEmailType ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, UserAccountID UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID), EmailTypeID INT NOT NULL REFERENCES EmailType(ID) ) GO INSERT INTO UserAccountEmailType SELECT EmailGroupUserAccount.UserAccountID, EmailGroupType.EmailTypeID FROM EmailGroupUserAccount JOIN EmailGroupType ON EmailGroupUserAccount.EmailGroupID = EmailGroupType.EmailGroupID GO INSERT INTO EventEmailParameters(EmailTypeID) SELECT EmailType.ID FROM EmailType JOIN EmailCategory ON EmailType.EmailCategoryID = EmailCategory.ID WHERE EmailCategory.Name = 'Event' GO UPDATE EventEmailParameters SET TriggersEmailSQL = 'SELECT CASE WHEN EventType.Name = ''Fault'' THEN 1 ELSE 0 END FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID WHERE Event.ID = {0}' WHERE EmailTypeID = (SELECT ID FROM EmailType WHERE XSLTemplateID = (SELECT ID FROM XSLTemplate WHERE Name = 'Default Fault')) GO UPDATE EventEmailParameters SET EventDetailSQL = 'DECLARE @timeTolerance FLOAT = (SELECT CAST(Value AS FLOAT) FROM Setting WHERE Name = ''TimeTolerance'') DECLARE @lineID INT DECLARE @startTime DATETIME2 DECLARE @endTime DATETIME2 SELECT @lineID = LineID, @startTime = dbo.AdjustDateTime2(StartTime, -@timeTolerance), @endTime = dbo.AdjustDateTime2(EndTime, @timeTolerance) FROM Event WHERE ID = {0} SELECT * INTO #lineEvent FROM Event WHERE Event.LineID = @lineID AND Event.EndTime >= @startTime AND Event.StartTime <= @endTime SELECT ROW_NUMBER() OVER(PARTITION BY Event.MeterID ORDER BY FaultSummary.Inception) AS FaultNumber, FaultSummary.ID AS FaultSummaryID, Meter.AssetKey AS MeterKey, Meter.Name AS MeterName, MeterLocation.AssetKey as StationKey, MeterLocation.Name AS StationName, Line.AssetKey AS LineKey, MeterLine.LineName, FaultSummary.FaultType, FaultSummary.Inception, FaultSummary.DurationCycles, FaultSummary.DurationSeconds * 1000.0 AS DurationMilliseconds, CASE WHEN FaultSummary.PrefaultCurrent <> -1E308 THEN FORMAT(FaultSummary.PrefaultCurrent, ''0.##########'') ELSE ''NaN'' END AS PrefaultCurrent, CASE WHEN FaultSummary.PostfaultCurrent <> -1E308 THEN FORMAT(FaultSummary.PostfaultCurrent, ''0.##########'') ELSE ''NaN'' END AS PostfaultCurrent, FaultSummary.ReactanceRatio, FaultSummary.CurrentMagnitude AS FaultCurrent, FaultSummary.Algorithm, FaultSummary.Distance AS SingleEndedDistance, DoubleEndedFaultSummary.Distance AS DoubleEndedDistance, DoubleEndedFaultSummary.Angle AS DoubleEndedAngle, RIGHT(DataFile.FilePath, CHARINDEX(CHAR(92), REVERSE(DataFile.FilePath)) - 1) AS FileName, FaultSummary.EventID, Event.StartTime AS EventStartTime, Event.EndTime AS EventEndTime INTO #summaryData FROM #lineEvent Event JOIN EventType ON Event.EventTypeID = EventType.ID AND EventType.Name = ''Fault'' JOIN FaultSummary ON FaultSummary.EventID = Event.ID AND FaultSummary.IsSelectedAlgorithm <> 0 JOIN DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN Meter ON Event.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Event.LineID JOIN Line ON MeterLine.LineID = Line.ID LEFT OUTER JOIN DoubleEndedFaultDistance ON DoubleEndedFaultDistance.LocalFaultSummaryID = FaultSummary.ID LEFT OUTER JOIN DoubleEndedFaultSummary ON DoubleEndedFaultSummary.ID = DoubleEndedFaultDistance.ID WHERE DataFile.FilePath LIKE ''%.DAT'' OR DataFile.FilePath LIKE ''%.D00'' OR DataFile.FilePath LIKE ''%.PQD'' OR DataFile.FilePath LIKE ''%.RCD'' OR DataFile.FilePath LIKE ''%.RCL'' OR DataFile.FilePath LIKE ''%.SEL'' OR DataFile.FilePath LIKE ''%.EVE'' OR DataFile.FilePath LIKE ''%.CEV'' DECLARE @url VARCHAR(MAX) = (SELECT Value FROM DashSettings WHERE Name = ''System.URL'') SELECT ( SELECT ID AS [@id] FROM #lineEvent FOR XML PATH(''Event''), TYPE ) AS [Events], ( SELECT FaultNumber AS [@num], ( SELECT MeterKey, MeterName, StationKey, StationName, LineKey, LineName, FaultType, Inception, DurationCycles, DurationMilliseconds, PrefaultCurrent, PostfaultCurrent, ReactanceRatio, FaultCurrent, Algorithm, SingleEndedDistance, DoubleEndedDistance, DoubleEndedAngle, EventStartTime, EventEndTime, FileName, EventID, FaultSummaryID AS FaultID FROM #summaryData WHERE FaultNumber = Fault.FaultNumber FOR XML PATH(''SummaryData''), TYPE ) FROM ( SELECT DISTINCT FaultNumber FROM #summaryData ) Fault FOR XML PATH(''Fault''), TYPE ) AS [Faults], MeterLine.LineName AS [Line/Name], Line.AssetKey AS [Line/AssetKey], FORMAT(Line.Length, ''0.##########'') AS [Line/Length], FORMAT(SQRT(LineImpedance.R1 * LineImpedance.R1 + LineImpedance.X1 * LineImpedance.X1), ''0.##########'') AS [Line/Z1], CASE LineImpedance.R1 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2(LineImpedance.X1, LineImpedance.R1) * 180 / PI(), ''0.##########'') END AS [Line/A1], FORMAT(LineImpedance.R1, ''0.##########'') AS [Line/R1], FORMAT(LineImpedance.X1, ''0.##########'') AS [Line/X1], FORMAT(SQRT(LineImpedance.R0 * LineImpedance.R0 + LineImpedance.X0 * LineImpedance.X0), ''0.##########'') AS [Line/Z0], CASE LineImpedance.R0 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2(LineImpedance.X0, LineImpedance.R0) * 180 / PI(), ''0.##########'') END AS [Line/A0], FORMAT(LineImpedance.R0, ''0.##########'') AS [Line/R0], FORMAT(LineImpedance.X0, ''0.##########'') AS [Line/X0], FORMAT(SQRT(POWER((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, 2) + POWER((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, 2)), ''0.##########'') AS [Line/ZS], CASE 2.0 * LineImpedance.R1 + LineImpedance.R0 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, (2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0) * 180 / PI(), ''0.##########'') END AS [Line/AS], FORMAT((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, ''0.##########'') AS [Line/RS], FORMAT((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, ''0.##########'') AS [Line/XS], @url AS [PQDashboard] FROM Event JOIN Line ON Event.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = Event.MeterID AND MeterLine.LineID = Event.LineID JOIN LineImpedance ON LineImpedance.LineID = Line.ID WHERE Event.ID = {0} FOR XML PATH(''EventDetail''), TYPE' WHERE EmailTypeID = (SELECT ID FROM EmailType WHERE XSLTemplateID = (SELECT ID FROM XSLTemplate WHERE Name = 'Default Fault')) GO DELETE FROM DataOperation WHERE TypeName = 'FaultData.DataWriters.EventEmailWriter' GO CREATE TABLE SnapshotHarmonics ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, EventID INT NOT NULL REFERENCES Event(ID), ChannelID INT NOT NULL REFERENCES Channel(ID), SpectralData varchar(max) NULL ) GO CREATE NONCLUSTERED INDEX IX_SnapshotHarmonics_EventID ON SnapshotHarmonics(EventID ASC) GO INSERT INTO EventType(Name, Description) VALUES ('BreakerOpen', 'Breaker Opening - Nonfault') GO INSERT INTO EventType(Name, Description) VALUES ('Snapshot', 'Snapshot') GO CREATE TABLE FaultCauseMetrics ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, EventID INT NOT NULL REFERENCES Event(ID), FaultNumber INT NOT NULL, TreeFaultResistance FLOAT NULL, LightningMilliseconds FLOAT NULL, InceptionDistanceFromPeak FLOAT NULL, PrefaultThirdHarmonic FLOAT NULL, GroundCurrentRatio FLOAT NULL, LowPrefaultCurrentRatio FLOAT NULL ) GO CREATE NONCLUSTERED INDEX IX_FaultCauseMetrics_EventID ON FaultCauseMetrics(EventID ASC) GO CREATE TABLE Unit ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name VARCHAR(MAX) NOT NULL ) GO CREATE TABLE PQMeasurement ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name VARCHAR(MAX) NOT NULL, Description VARCHAR(MAX) NULL, UnitID INT NOT NULL REFERENCES Unit(ID), MeasurementTypeID INT NOT NULL REFERENCES MeasurementType(ID), MeasurementCharacteristicID INT NOT NULL REFERENCES MeasurementCharacteristic(ID), PhaseID INT NOT NULL REFERENCES Phase(ID), HarmonicGroup INT NOT NULL DEFAULT 0, Enabled bit NOT NULL DEFAULT 1 ) GO CREATE TABLE PQTrendStat ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, MeterID INT NOT NULL REFERENCES Meter(ID), PQMeasurementTypeID INT NOT NULL REFERENCES PQMeasurement(ID), Date DATE NOT NULL, Max FLOAT NULL, CP99 FLOAT NULL, CP95 FLOAT NULL, Avg FLOAT NULL, CP05 FLOAT NULL, CP01 FLOAT NULL, Min FLOAT NULL ) GO CREATE NONCLUSTERED INDEX IX_PQTrendStat_Date ON PQTrendStat(Date ASC) GO CREATE NONCLUSTERED INDEX IX_PQTrendStat_MeterID_Date ON PQTrendStat(MeterID ASC, Date ASC) GO CREATE NONCLUSTERED INDEX IX_PQTrendStat_MeterID_PQMeasurementTypeID_Date ON PQTrendStat(Date DESC, MeterID ASC, PQMeasurementTypeID ASC) GO INSERT INTO Unit (Name) VALUES ('Volts') GO INSERT INTO Unit (Name) VALUES ('Amps') GO INSERT INTO Unit (Name) VALUES ('KW') GO INSERT INTO Unit (Name) VALUES ('KVAR') GO INSERT INTO Unit (Name) VALUES ('KVA') GO INSERT INTO Unit (Name) VALUES ('Per Unit') GO INSERT INTO Unit (Name) VALUES ('Percent') GO CREATE TABLE StepChangeMeasurement( ID INT PRIMARY KEY IDENTITY(1,1), PQMeasurementID INT FOREIGN KEY REFERENCES PQMeasurement(ID) NOT NULL, Setting FLOAT NULL ) GO CREATE NONCLUSTERED INDEX IX_StepChangeMeasurement_PQMeasurement ON StepChangeMeasurement(PQMeasurementID ASC) GO CREATE TABLE StepChangeStat( ID INT PRIMARY KEY IDENTITY(1,1), MeterID INT FOREIGN KEY REFERENCES Meter(ID) NOT NULL, Date Date NOT NULL, StepChangeMeasurementID INT FOREIGN KEY REFERENCES StepChangeMeasurement(ID) NOT NULL, Value FLOAT NULL ) GO CREATE NONCLUSTERED INDEX IX_StepChangeStat_Date ON StepChangeStat(Date ASC) GO CREATE NONCLUSTERED INDEX IX_StepChangeStat_MeterID_Date ON StepChangeStat(MeterID ASC, Date ASC) GO CREATE NONCLUSTERED INDEX IX_StepChangeStat_MeterID_StepChangeMeasurementID_Date ON StepChangeStat(MeterID ASC,StepChangeMeasurementID ASC, Date ASC) GO CREATE TABLE Report ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, MeterID INT NOT NULL REFERENCES Meter(ID), Month INT NOT NULL, Year INT NOT NULL, Results VARCHAR(4) NOT NULL, PDF VARBINARY(MAX) NOT NULL, CONSTRAINT UC_Report UNIQUE(ID, MeterID, Month, Year) ) GO CREATE TABLE EventStat ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, EventID INT NOT NULL REFERENCES Event(ID), VPeak FLOAT NULL, VAMax FLOAT NULL, VBMax FLOAT NULL, VCMax FLOAT NULL, VABMax FLOAT NULL, VBCMax FLOAT NULL, VCAMax FLOAT NULL, VAMin FLOAT NULL, VBMin FLOAT NULL, VCMin FLOAT NULL, VABMin FLOAT NULL, VBCMin FLOAT NULL, VCAMin FLOAT NULL, IPeak FLOAT NULL, IAMax FLOAT NULL, IBMax FLOAT NULL, ICMax FLOAT NULL, IA2t FLOAT NULL, IB2t FLOAT NULL, IC2t FLOAT NULL, InitialMW FLOAT NULL, FinalMW FLOAT NULL, PQViewID INT NULL, CONSTRAINT UC_EventStat_EventID UNIQUE(EventID) ) GO ALTER FUNCTION ComputeHash ( @eventID INT, @templateID INT ) RETURNS BIGINT BEGIN DECLARE @md5Hash BINARY(16) DECLARE @eventDetailSQL VARCHAR(MAX) = ( SELECT EventDetailSQL FROM EmailType JOIN EventEmailParameters ON EventEmailParameters.EmailTypeID = EmailType.ID WHERE EmailType.XSLTemplateID = @templateID ) DECLARE @eventDetail VARCHAR(MAX) EXEC sp_executesql @eventDetailSQL, @eventID, @eventDetail OUT SELECT @md5Hash = master.sys.fn_repl_hash_binary(CONVERT(VARBINARY(MAX), @eventDetail)) FROM EventDetail WHERE EventID = @eventID SELECT @md5Hash = master.sys.fn_repl_hash_binary(@md5Hash + CONVERT(VARBINARY(MAX), Template)) FROM XSLTemplate WHERE ID = @templateID RETURN CONVERT(BIGINT, SUBSTRING(@md5Hash, 0, 8)) ^ CONVERT(BIGINT, SUBSTRING(@md5Hash, 8, 8)) END GO CREATE FUNCTION RecursiveMeterSearch(@assetGroupID int) RETURNS TABLE AS RETURN WITH AssetGroupHeirarchy AS ( SELECT ParentAssetGroupID, ChildAssetGroupID FROM AssetGroupAssetGroup WHERE ParentAssetGroupID = @assetGroupID -- anchor member UNION ALL SELECT b.ParentAssetGroupID, a.ChildAssetGroupID -- recursive member FROM AssetGroupAssetGroup AS a JOIN AssetGroupHeirarchy AS b ON b.ChildAssetGroupID = a.ParentAssetGroupID ) SELECT DISTINCT MeterID AS ID FROM MeterAssetGroup LEFT JOIN AssetGroupHeirarchy ON MeterAssetGroup.AssetGroupID = AssetGroupHeirarchy.ChildAssetGroupID WHERE MeterAssetGroup.AssetGroupID = @assetGroupID OR MeterAssetGroup.AssetGroupID IN (SELECT ChildAssetGroupID FROM AssetGroupHeirarchy) GO ALTER VIEW MeterLineDetail AS SELECT MeterLine.ID, MeterLine.MeterID, Meter.AssetKey AS MeterKey, Meter.Name AS MeterName, MeterLine.LineID, Line.AssetKey AS LineKey, MeterLine.LineName, FaultDetectionLogic.Expression as FaultDetectionLogic FROM MeterLine JOIN Meter ON MeterLine.MeterID = Meter.ID JOIN Line ON MeterLIne.LineID = Line.ID LEFT JOIN FaultDetectionLogic ON FaultDetectionLogic.MeterLineID = MeterLine.ID GO CREATE VIEW AssetGroupAssetGroupView AS SELECT AssetGroupAssetGroup.ID, AssetGroupAssetGroup.ParentAssetGroupID, AssetGroupAssetGroup.ChildAssetGroupID, Parent.Name as ParentAssetGroupName, Child.Name as ChildAssetGroupName FROM AssetGroupAssetGroup JOIN AssetGroup as Parent ON AssetGroupAssetGroup.ParentAssetGroupID = Parent.ID JOIN AssetGroup as Child ON AssetGroupAssetGroup.ChildAssetGroupID = Child.ID GO CREATE VIEW MeterAssetGroupView AS SELECT MeterAssetGroup.ID, Meter.Name AS MeterName, Meter.ID AS MeterID, AssetGroupID, MeterLocation.Name AS Location FROM MeterAssetGroup JOIN Meter ON MeterAssetGroup.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID GO CREATE VIEW LineAssetGroupView AS SELECT LineAssetGroup.ID, Line.AssetKey AS LineName, (SELECT TOP 1 LineName FROM MeterLine Where LineID = Line.ID) AS LongLineName, Line.ID AS LineID, AssetGroupID FROM LineAssetGroup JOIN Line ON LineAssetGroup.LineID = Line.ID GO CREATE VIEW UserAccountAssetGroupView AS SELECT UserAccountAssetGroup.ID, UserAccountAssetGroup.UserAccountID, UserAccountAssetGroup.AssetGroupID, UserAccountAssetGroup.Dashboard, UserAccountAssetGroup.Email, UserAccount.Name AS Username, AssetGroup.Name AS GroupName FROM UserAccountAssetGroup JOIN UserAccount ON UserAccountAssetGroup.UserAccountID = UserAccount.ID JOIN AssetGroup ON UserAccountAssetGroup.AssetGroupID = AssetGroup.ID GO ALTER VIEW UserMeter AS SELECT DISTINCT UserAccount.Name AS UserName, Meter.ID AS MeterID FROM UserAccount JOIN UserAccountAssetGroup ON UserAccountAssetGroup.UserAccountID = UserAccount.ID LEFT OUTER JOIN MeterAssetGroup ON MeterAssetGroup.AssetGroupID = UserAccountAssetGroup.AssetGroupID LEFT OUTER JOIN LineAssetGroup ON LineAssetGroup.AssetGroupID = UserAccountAssetGroup.AssetGroupID LEFT OUTER JOIN MeterLine ON MeterLine.LineID = LineAssetGroup.LineID JOIN Meter ON MeterAssetGroup.MeterID = Meter.ID OR MeterLine.MeterID = Meter.ID WHERE UserAccount.Approved <> 0 AND UserAccountAssetGroup.Dashboard <> 0 GO ALTER VIEW DisturbanceView AS SELECT Disturbance.ID, Disturbance.EventID, Disturbance.EventTypeID, Disturbance.PhaseID, Disturbance.Magnitude, Disturbance.PerUnitMagnitude, Disturbance.StartTime, Disturbance.EndTime, Disturbance.DurationSeconds, Disturbance.DurationCycles, Disturbance.StartIndex, Disturbance.EndIndex, Event.MeterID, ( SELECT MAX(SeverityCode) AS Expr1 FROM DisturbanceSeverity WHERE (DisturbanceID = Disturbance.ID) ) AS SeverityCode, Meter.Name AS MeterName, Phase.Name AS PhaseName, Event.LineID FROM Disturbance JOIN Event ON Disturbance.EventID = Event.ID JOIN Meter ON Event.MeterID = Meter.ID JOIN Phase ON Disturbance.PhaseID = Phase.ID GO CREATE VIEW BreakerView AS SELECT BreakerOperation.ID, Meter.ID AS MeterID, Event.ID AS EventID, EventType.Name AS EventType, BreakerOperation.TripCoilEnergized AS Energized, BreakerOperation.BreakerNumber, MeterLine.LineName, Phase.Name AS PhaseName, CAST(BreakerOperation.BreakerTiming AS DECIMAL(16, 5)) AS Timing, BreakerOperation.BreakerSpeed AS Speed, BreakerOperationType.Name AS OperationType, BreakerOperation.UpdatedBy FROM BreakerOperation JOIN Event ON BreakerOperation.EventID = Event.ID JOIN EventType ON EventType.ID = Event.EventTypeID JOIN Meter ON Meter.ID = Event.MeterID JOIN Line ON Line.ID = Event.LineID JOIN MeterLine ON MeterLine.LineID = Event.LineID AND MeterLine.MeterID = Meter.ID JOIN BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN Phase ON BreakerOperation.PhaseID = Phase.ID GO ALTER VIEW FaultView AS SELECT FaultSummary.ID AS ID, FaultSummary.EventID, FaultSummary.Algorithm, FaultSummary.FaultNumber, FaultSummary.CalculationCycle, FaultSummary.Distance, FaultSummary.CurrentMagnitude, FaultSummary.CurrentLag, FaultSummary.PrefaultCurrent, FaultSummary.PostfaultCurrent, FaultSummary.Inception, FaultSummary.DurationSeconds, FaultSummary.DurationCycles, FaultSummary.FaultType, FaultSummary.IsSelectedAlgorithm, FaultSummary.IsValid, FaultSummary.IsSuppressed, Meter.Name AS MeterName, Meter.ShortName AS ShortName, MeterLocation.ShortName AS LocationName, Meter.ID AS MeterID, Line.ID AS LineID, MeterLine.LineName AS LineName, Line.VoltageKV AS Voltage, Event.StartTime AS InceptionTime, CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS NVARCHAR(19)) END AS CurrentDistance, ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY FaultSummary.IsSuppressed, FaultSummary.IsSelectedAlgorithm DESC, FaultSummary.Inception) AS RK FROM FaultSummary JOIN Event ON FaultSummary.EventID = Event.ID JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Meter ON Event.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN Line ON Event.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID WHERE EventType.Name = 'Fault' GO ALTER VIEW WorkbenchVoltageCurveView AS SELECT WorkbenchVoltageCurve.ID, WorkbenchVoltageCurve.Name, WorkbenchVoltageCurvePoint.ID AS CurvePointID, WorkbenchVoltageCurvePoint.PerUnitMagnitude, WorkbenchVoltageCurvePoint.DurationSeconds, WorkbenchVoltageCurvePoint.LoadOrder, WorkbenchVoltageCurve.Visible FROM WorkbenchVoltageCurve JOIN WorkbenchVoltageCurvePoint ON WorkbenchVoltageCurve.ID = WorkbenchVoltageCurvePoint.VoltageCurveID AND WorkbenchVoltageCurve.ID = WorkbenchVoltageCurvePoint.VoltageCurveID AND WorkbenchVoltageCurve.ID = WorkbenchVoltageCurvePoint.VoltageCurveID GO ALTER VIEW EmailTypeView AS SELECT EmailType.EmailCategoryID, EmailType.XSLTemplateID, EmailType.ID, EmailCategory.Name AS EmailCategory, XSLTemplate.Name AS XSLTemplate, EmailCategory.Name + ' - ' + XSLTemplate.Name AS Name FROM EmailType JOIN EmailCategory ON EmailType.EmailCategoryID = EmailCategory.ID JOIN XSLTemplate ON EmailType.XSLTemplateID = XSLTemplate.ID GO ALTER VIEW AuditLogView AS SELECT TOP(2000) ID, TableName, PrimaryKeyColumn, PrimaryKeyValue, ColumnName, OriginalValue, NewValue, Deleted, UpdatedBy, UpdatedOn FROM AuditLog WHERE UpdatedBy IS NOT NULL GO ALTER VIEW MetersWithHourlyLimits AS SELECT Meter.Name, COUNT(DISTINCT Channel.ID) AS Limits, Meter.ID FROM HourOfWeekLimit JOIN Channel ON HourOfWeekLimit.ChannelID = Channel.ID JOIN Meter ON Channel.MeterID = Meter.ID GROUP BY Meter.Name, Meter.ID GO ALTER VIEW HourOfWeekLimitView AS SELECT HourOfWeekLimit.ID, HourOfWeekLimit.ChannelID, HourOfWeekLimit.AlarmTypeID, HourOfWeekLimit.HourOfWeek, HourOfWeekLimit.Severity, HourOfWeekLimit.High, HourOfWeekLimit.Low, HourOfWeekLimit.Enabled, AlarmType.Name AS AlarmTypeName FROM HourOfWeekLimit JOIN AlarmType ON HourOfWeekLimit.AlarmTypeID = AlarmType.ID GO ALTER VIEW ChannelsWithHourlyLimits AS SELECT Channel.Name, COUNT(DISTINCT HourOfWeekLimit.HourOfWeek) AS Limits, Channel.ID, Channel.MeterID, AlarmType.Name AS AlarmTypeName, MeasurementCharacteristic.Name AS MeasurementCharacteristic, MeasurementType.Name AS MeasurementType, Channel.HarmonicGroup, Phase.Name AS Phase FROM HourOfWeekLimit JOIN Channel ON HourOfWeekLimit.ChannelID = Channel.ID JOIN AlarmType ON HourOfWeekLimit.AlarmTypeID = AlarmType.ID JOIN Meter ON Channel.MeterID = Meter.ID JOIN MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN Phase ON Channel.PhaseID = Phase.ID GROUP BY Channel.Name, Channel.MeterID, Channel.ID, AlarmType.Name, MeasurementCharacteristic.Name, MeasurementType.Name, Channel.HarmonicGroup, Phase.Name GO ALTER VIEW MetersWithNormalLimits AS SELECT Meter.Name, COUNT(DISTINCT Channel.ID) AS Limits, Meter.ID FROM AlarmRangeLimit JOIN Channel ON AlarmRangeLimit.ChannelID = Channel.ID JOIN Meter ON Channel.MeterID = Meter.ID GROUP BY Meter.Name, Meter.ID GO ALTER VIEW ChannelsWithNormalLimits AS SELECT Channel.Name, Channel.ID, Channel.MeterID, AlarmType.Name AS AlarmTypeName, MeasurementCharacteristic.Name AS MeasurementCharacteristic, MeasurementType.Name AS MeasurementType, Channel.HarmonicGroup, Phase.Name AS Phase, High, Low, RangeInclusive, PerUnit, AlarmRangeLimit.Enabled, IsDefault FROM AlarmRangeLimit JOIN Channel ON AlarmRangeLimit.ChannelID = Channel.ID JOIN AlarmType ON AlarmRangeLimit.AlarmTypeID = AlarmType.ID JOIN Meter ON Channel.MeterID = Meter.ID JOIN MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN Phase ON Channel.PhaseID = Phase.ID GO ALTER PROCEDURE [dbo].[selectBreakersForCalendar] @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @counter INT = 0 DECLARE @eventDate DATE = (Select max(CAST(StartTime AS Date)) from Event) --'2015-03-23' DECLARE @numberOfDays INT = DATEDIFF ( day , (Select min(CAST(StartTime AS Date)) from Event), @eventDate) --365*5 SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate) CREATE TABLE #temp(Date DATE) WHILE (@counter <= @numberOfDays) BEGIN INSERT INTO #temp VALUES(@eventDate) SET @eventDate = DATEADD(DAY, 1, @eventDate) SET @counter = @counter + 1 END SELECT Date as thedate, Normal as normal, late as late, indeterminate as indeterminate FROM ( SELECT #temp.Date, [BreakerOperationType].Name AS EventTypeName, COALESCE(EventCount, 0) AS EventCount FROM #temp CROSS JOIN [BreakerOperationType] LEFT OUTER JOIN ( SELECT CAST([TripCoilEnergized] AS Date) AS EventDate, [BreakerOperationTypeID] as EventTypeID, COUNT(*) AS EventCount FROM [BreakerOperation] join [Event] on [BreakerOperation].[EventID] = [Event].[ID] GROUP BY CAST([TripCoilEnergized] AS Date), [BreakerOperationTypeID] ) AS Event ON #temp.Date = Event.EventDate AND [BreakerOperationType].ID = Event.EventTypeID ) AS EventDate PIVOT ( SUM(EventCount) FOR EventDate.EventTypeName IN ( normal , late , indeterminate ) ) as pvt ORDER BY Date DROP TABLE #temp END GO ALTER PROCEDURE [dbo].[selectSiteLinesDisturbanceDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @worstPhaseID INT = (SELECT ID FROM Phase WHERE Name = 'Worst') DECLARE @startDate DATETIME = @EventDate DECLARE @endDate DATETIME IF @context = 'day' BEGIN SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0) SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0) SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate) SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope') SELECT Event.LineID AS thelineid, Event.ID AS theeventid, Disturbance.ID as disturbanceid, EventType.Name AS disturbancetype, Phase.Name AS phase, CASE Disturbance.PerUnitMagnitude WHEN -1E308 THEN 'NaN' ELSE CAST(Disturbance.PerUnitMagnitude AS VARCHAR(MAX)) END AS magnitude, CASE Disturbance.DurationSeconds WHEN -1E308 THEN 'NaN' ELSE CAST(CONVERT(DECIMAL(10,3), Disturbance.DurationSeconds) AS VARCHAR(14)) END AS duration, CAST(Disturbance.StartTime AS VARCHAR(26)) AS theinceptiontime, dbo.DateDiffTicks('1970-01-01', Disturbance.StartTime) / 10000.0 AS startmillis, dbo.DateDiffTicks('1970-01-01', Disturbance.EndTime) / 10000.0 AS endmillis, DisturbanceSeverity.SeverityCode, MeterLine.LineName + ' ' + [Line].[AssetKey] AS thelinename, Line.VoltageKV AS voltage, (SELECT COUNT(*) FROM EventNote WHERE EventID = Event.ID) as notes FROM Event JOIN Disturbance ON Disturbance.EventID = Event.ID JOIN Disturbance WorstDisturbance ON Disturbance.EventID = WorstDisturbance.EventID AND Disturbance.PerUnitMagnitude = WorstDisturbance.PerUnitMagnitude AND Disturbance.DurationSeconds = WorstDisturbance.DurationSeconds JOIN EventType ON Disturbance.EventTypeID = EventType.ID JOIN Phase ON Disturbance.PhaseID = Phase.ID JOIN DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN Meter ON Meter.ID = @MeterID JOIN Line ON Event.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = @MeterID AND MeterLine.LineID = Line.ID JOIN VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID WHERE Event.StartTime >= @startDate AND Event.StartTime < @endDate AND Event.MeterID = @MeterID AND WorstDisturbance.PhaseID = @worstPhaseID AND Disturbance.PhaseID <> @worstPhaseID AND VoltageEnvelope.Name = COALESCE(@voltageEnvelope, 'ITIC') ORDER BY Event.StartTime ASC END GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Breakers.MaxCyclesBeforeRestrike', '2.0', '2.0') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Breakers.MinCyclesBeforeRestrike', '0.125', '0.125') GO UPDATE Setting SET Name = 'COMTRADE.MinWaitTime' WHERE Name = 'COMTRADEMinWaitTime' GO UPDATE Setting SET Name = 'COMTRADE.UseRelaxedValidation' WHERE Name = 'COMTRADEUseRelaxedValidation' GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('COMTRADE.WaitForINF', 'False', 'False') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Email.AdminAddress', 'xda-admin@gridprotectionalliance.org', 'xda-admin@gridprotectionalliance.org') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Email.BlindCopyAddress', '', '') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('EventEmail.Enabled', 'False', 'False') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('EventEmail.MaxEmailCount', '0', '0') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('EventEmail.MaxEmailSpan', '0.0', '0.0') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.FaultCalculationCycleMethod', 'MaxCurrent', 'MaxCurrent') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.FaultClearingAdjustmentSamples', '10', '10') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.FaultedVoltageThreshold', '0.8', '0.8') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.GroundedFaultVoltageThreshold', '0.001', '0.001') GO UPDATE Setting SET Name = 'PQMarkAggregation.Enabled' WHERE Name = 'PQMarkAggregation.Enable' GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('PQTrendingWebReport.Enabled', 'False', 'False') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('PQTrendingWebReport.Frequency', '0 2 * * *', '0 2 * * *') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('PQTrendingWebReport.Verbose', 'False', 'False') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('StepChangeWebReport.Enabled', 'False', 'False') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('StepChangeWebReport.Frequency', '0 2 * * *', '0 2 * * *') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('StepChangeWebReport.Verbose', 'False', 'False') GO INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChart', 'BreakerOpen', 1) GO INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChart', 'Snapshot', 1) GO INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChartColors', 'BreakerOpen,#B245BA', 1) GO INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChartColors', 'Snapshot,#9db087', 1) GO INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('System.URL', 'http://localhost/PQDashboard', 1) GO INSERT INTO Phase(Name, Description) SELECT 'NG', 'Neutral to ground' WHERE NOT EXISTS (SELECT * FROM Phase WHERE Name = 'NG') GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average RMS Voltage Phase A', 'Average RMS Voltage Phase A', (SELECT ID FROM Unit WHERE Name = 'Volts'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'RMS'), (SELECT ID FROM Phase WHERE Name = 'AN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average RMS Voltage Phase B', 'Average RMS Voltage Phase B', (SELECT ID FROM Unit WHERE Name = 'Volts'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'RMS'), (SELECT ID FROM Phase WHERE Name = 'BN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average RMS Voltage Phase C', 'Average RMS Voltage Phase C', (SELECT ID FROM Unit WHERE Name = 'Volts'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'RMS'), (SELECT ID FROM Phase WHERE Name = 'CN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Voltage Unbalance(S2/S1)', 'Average Voltage Unbalance(S2/S1)', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1'), (SELECT ID FROM Phase WHERE Name = 'None'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Voltage Unbalance(S0/S1)', 'Average Voltage Unbalance(S0/S1)', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1'), (SELECT ID FROM Phase WHERE Name = 'None'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Pst Flicker Phase A', 'Pst Flicker Phase A', (SELECT ID FROM Unit WHERE Name = 'Per Unit'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'FlkrPST'), (SELECT ID FROM Phase WHERE Name = 'AN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Pst Flicker Phase B', 'Pst Flicker Phase B', (SELECT ID FROM Unit WHERE Name = 'Per Unit'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'FlkrPST'), (SELECT ID FROM Phase WHERE Name = 'BN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Pst Flicker Phase C', 'Pst Flicker Phase C', (SELECT ID FROM Unit WHERE Name = 'Per Unit'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'FlkrPST'), (SELECT ID FROM Phase WHERE Name = 'CN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Vthd Phase A', 'Average Vthd Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'AN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Vthd Phase B', 'Average Vthd Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'BN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Vthd Phase C', 'Average Vthd Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'CN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H3 Phase A', 'Average V H3 Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 3) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H3 Phase B', 'Average V H3 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 3) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H3 Phase C', 'Average V H3 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 3) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H5 Phase A', 'Average V H5 Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 5) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H5 Phase B', 'Average V H5 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 5) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H5 Phase C', 'Average V H5 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 5) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H7 Phase A', 'Average V H7 Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 7) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H7 Phase B', 'Average V H7 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 7) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H7 Phase C', 'Average V H7 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 7) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H11 Phase A', 'Average V H11 Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 11) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H11 Phase B', 'Average V H11 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 11) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H11 Phase C', 'Average V H11 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 11) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H13 Phase A', 'Average V H13 Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 13) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H13 Phase B', 'Average V H13 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 13) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H13 Phase C', 'Average V H13 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 13) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Current Unbalance(S2/S1)', 'Average Current Unbalance(S2/S1)', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1'), (SELECT ID FROM Phase WHERE Name = 'None'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Current Unbalance(S0/S1)', 'Average Current Unbalance(S0/S1)', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1'), (SELECT ID FROM Phase WHERE Name = 'None'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Ithd Phase A', 'Average Ithd Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'AN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Ithd Phase B', 'Average Ithd Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'BN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Ithd Phase C', 'Average Ithd Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'CN'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Ithd Phase N', 'Average Ithd Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'NG'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Ithd Phase RES', 'Average Ithd Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'RES'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H3 Phase A', 'Average I H3 Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 3) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H3 Phase B', 'Average I H3 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 3) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H3 Phase C', 'Average I H3 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 3) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H3 Phase N', 'Average I H3 Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'NG'), 3) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H3 Phase RES', 'Average I H3 Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'RES'), 3) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H5 Phase A', 'Average I H5 Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 5) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H5 Phase B', 'Average I H5 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 5) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H5 Phase C', 'Average I H5 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 5) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H5 Phase N', 'Average I H5 Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'NG'), 5) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H5 Phase RES', 'Average I H5 Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'RES'), 5) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H7 Phase A', 'Average I H7 Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 7) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H7 Phase B', 'Average I H7 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 7) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H7 Phase C', 'Average I H7 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 7) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H7 Phase N', 'Average I H7 Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'NG'), 7) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H7 Phase RES', 'Average I H7 Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'RES'), 7) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H11 Phase A', 'Average I H11 Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 11) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H11 Phase B', 'Average I H11 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 11) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H11 Phase C', 'Average I H11 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 11) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H11 Phase N', 'Average I H11 Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'NG'), 11) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H11 Phase RES', 'Average I H11 Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'RES'), 11) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H13 Phase A', 'Average I H13 Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 13) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H13 Phase B', 'Average I H13 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 13) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H13 Phase C', 'Average I H13 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 13) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H13 Phase N', 'Average I H13 Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'NG'), 13) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H13 Phase RES', 'Average I H13 Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'RES'), 13) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('P', 'P', (SELECT ID FROM Unit WHERE Name = 'KW'), (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'P'), (SELECT ID FROM Phase WHERE Name = 'LineToNeutralAverage'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Q', 'Q', (SELECT ID FROM Unit WHERE Name = 'KVAR'), (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'Q'), (SELECT ID FROM Phase WHERE Name = 'LineToNeutralAverage'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('S', 'S', (SELECT ID FROM Unit WHERE Name = 'KVA'), (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S'), (SELECT ID FROM Phase WHERE Name = 'LineToNeutralAverage'), 0) GO INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('PF', 'PF', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'PF'), (SELECT ID FROM Phase WHERE Name = 'LineToNeutralAverage'), 0) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average RMS Voltage Phase A'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average RMS Voltage Phase B'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average RMS Voltage Phase C'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Pst Flicker Phase A'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Pst Flicker Phase B'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Pst Flicker Phase C'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Vthd Phase A'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Vthd Phase B'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Vthd Phase C'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Voltage Unbalance(S2/S1)'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Voltage Unbalance(S0/S1)'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Current Unbalance(S2/S1)'), 5) GO INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Current Unbalance(S0/S1)'), 5) GO DROP TABLE MeterFileGroup GO CREATE TABLE MeterConfiguration ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, MeterID INT NOT NULL REFERENCES Meter(ID), DiffID INT NULL REFERENCES MeterConfiguration(ID), ConfigKey VARCHAR(50) NOT NULL, ConfigText VARCHAR(MAX) NOT NULL ) GO CREATE NONCLUSTERED INDEX IX_MeterConfiguration_MeterID ON MeterConfiguration(MeterID) GO CREATE NONCLUSTERED INDEX IX_MeterConfiguration_DiffID ON MeterConfiguration(DiffID) GO CREATE TABLE FileGroupMeterConfiguration ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, FileGroupID INT NOT NULL REFERENCES FileGroup(ID), MeterConfigurationID INT NOT NULL REFERENCES MeterConfiguration(ID) ) GO CREATE NONCLUSTERED INDEX IX_FileGroupMeterConfiguration_FileGroupID ON FileGroupMeterConfiguration(FileGroupID) GO CREATE NONCLUSTERED INDEX IX_FileGroupMeterConfiguration_MeterConfigurationID ON FileGroupMeterConfiguration(MeterConfigurationID) GO CREATE TABLE BreakerRestrike ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, EventID INT NOT NULL REFERENCES Event(ID), PhaseID INT NOT NULL REFERENCES Phase(ID), Sample INT NOT NULL, Timestamp DATETIME2 NOT NULL ) GO CREATE NONCLUSTERED INDEX IX_BreakerRestrike_EventID ON BreakerRestrike(EventID ASC) GO CREATE NONCLUSTERED INDEX IX_BreakerRestrike_Timestamp ON BreakerRestrike(Timestamp ASC) GO CREATE TABLE NoteType( ID int not null IDENTITY(1,1) PRIMARY KEY, Name varchar(max) not null, ReferenceTableName varchar(max) not null, ) GO INSERT INTO NoteType (Name, ReferenceTableName) VALUES ('Meter', 'Meter') GO INSERT INTO NoteType (Name, ReferenceTableName) VALUES ('Event', 'Event') GO CREATE TABLE Note ( ID int not null IDENTITY(1,1) PRIMARY KEY, NoteTypeID int Not NULL REFERENCES NoteType(ID), ReferenceTableID INT NOT NULL, Note VARCHAR(MAX) NOT NULL, UserAccount VARCHAR(MAX) NOT NULL DEFAULT SUSER_NAME(), Timestamp DATETIME NOT NULL DEFAULT GETUTCDATE(), ) GO CREATE NONCLUSTERED INDEX IX_Note_NoteTypeID_ReferenceTableID ON Note(NoteTypeID, ReferenceTableID) GO ALTER TABLE LinesToDataPush ADD RemoteLineCreatedByDataPusher bit NOT NULL DEFAULT (1) GO CREATE TABLE RelayAlertSetting ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, LineID INT NOT NULL REFERENCES Line(ID), TripTime INT NULL, PickupTime INT NULL, TripCoilCondition FLOAT NULL, ) GO CREATE TABLE PQMarkCompanyCustomer ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, PQMarkCompanyID INT NOT NULL FOREIGN KEY REFERENCES PQMarkCompany(ID), CustomerID INT NOT NULL ) GO CREATE NONCLUSTERED INDEX IX_PQMarkCompanyMeter_MeterID ON PQMarkCompanyMeter(MeterID) GO CREATE TABLE RelayPerformance ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, EventID INT NOT NULL REFERENCES Event(ID), ChannelID INT NOT NULL REFERENCES Channel(ID), Imax1 FLOAT NULL, Imax2 FLOAT NULL, TripInitiate DATETIME2 NULL, TripTime INT NULL, PickupTime INT NULL, TripCoilCondition FLOAT NULL, ) GO CREATE VIEW BreakerHistory AS SELECT Line.ID AS LineID, RelayPerformance.EventID AS EventID, RelayPerformance.Imax1, RelayPerformance.Imax2, RelayPerformance.TripInitiate, RelayPerformance.TripTime / 10 AS TripTime, RelayPerformance.PickupTime / 10 AS PickupTime, RelayPerformance.TripCoilCondition, RelayAlertSetting.TripCoilCondition AS TripCoilConditionAlert, RelayAlertSetting.TripTime AS TripTimeAlert, RelayAlertSetting.PickupTime AS PickupTimeAlert, RelayPerformance.ChannelID AS TripCoilChannelID FROM RelayPerformance LEFT OUTER JOIN Channel ON RelayPerformance.ChannelID = Channel.ID LEFT OUTER JOIN Line ON Channel.LineID = Line.ID LEFT OUTER JOIN RelayAlertSetting ON RelayAlertSetting.LineID = Line.ID GO ALTER VIEW LineView AS SELECT Line.ID, Line.AssetKey, Line.VoltageKV, Line.ThermalRating, Line.Length, COALESCE(Line.MaxFaultDistance, Line.Length * MaxFaultDistanceMultiplier.Value) MaxFaultDistance, COALESCE(Line.MinFaultDistance, Line.Length * MinFaultDistanceMultiplier.Value) MinFaultDistance, Line.Description, ( SELECT TOP 1 LineName FROM MeterLine WHERE LineID = Line.ID ) AS TopName, LineImpedance.R0, LineImpedance.X0, LineImpedance.R1, LineImpedance.X1, LineImpedance.ID AS LineImpedanceID, RelayAlertSetting.TripTime, RelayAlertSetting.PickupTime, RelayAlertSetting.TripCoilCondition, RelayAlertSetting.ID AS RelayAlertSettingID FROM Line LEFT OUTER JOIN LineImpedance ON Line.ID = LineImpedance.LineID LEFT OUTER JOIN RelayAlertSetting ON Line.ID = RelayAlertSetting.LineID CROSS JOIN (SELECT COALESCE((SELECT Value FROM Setting WHERE Name = 'FaultLocation.MaxFaultDistanceMultiplier'), 1.05) Value) MaxFaultDistanceMultiplier CROSS JOIN (SELECT COALESCE((SELECT Value FROM Setting WHERE Name = 'FaultLocation.MinFaultDistanceMultiplier'), 1.05) Value) MinFaultDistanceMultiplier GO CREATE VIEW OpenSEEScalarStatView AS SELECT Event.ID AS EventID, MeterLocation.Name AS Station, Meter.Name AS Meter, Line.AssetKey AS LineKey, MeterLine.LineName, EventType.Name AS [Event Type], FORMAT(DATEDIFF(MILLISECOND, Event.StartTime, Event.EndTime) / 1000.0, '0.###') AS [File Duration (seconds)], FORMAT(DATEDIFF(MILLISECOND, Event.StartTime, Event.EndTime) * System.Frequency / 1000.0, '0.##') AS [File Duration (cycles)], FORMAT(FaultSummary.Distance, '0.##') AS [Fault Distance (mi)], FORMAT(FaultSummary.DurationSeconds * 1000.0, '0') AS [Fault Duration (ms)], FORMAT(FaultSummary.DurationCycles, '0.##') AS [Fault Duration (cycles)], FORMAT(Sag.MagnitudePercent, '0.0') AS [Sag Magnitude (%)], FORMAT(Sag.MagnitudeVolts, '0') AS [Sag Magnitude (RMS volts)], FaultSummary.Algorithm, FORMAT(EventStat.VPeak, '0') AS [Voltage Peak (volts)], FORMAT(EventStat.VAMax, '0') AS [VA Maximum (RMS volts)], FORMAT(EventStat.VBMax, '0') AS [VB Maximum (RMS volts)], FORMAT(EventStat.VCMax, '0') AS [VC Maximum (RMS volts)], FORMAT(EventStat.VABMax, '0') AS [VAB Maximum (RMS volts)], FORMAT(EventStat.VBCMax, '0') AS [VBC Maximum (RMS volts)], FORMAT(EventStat.VCAMax, '0') AS [VCA Maximum (RMS volts)], FORMAT(EventStat.VAMin, '0') AS [VA Minimum (RMS volts)], FORMAT(EventStat.VBMin, '0') AS [VB Minimum (RMS volts)], FORMAT(EventStat.VCMin, '0') AS [VC Minimum (RMS volts)], FORMAT(EventStat.VABMin, '0') AS [VAB Minimum (RMS volts)], FORMAT(EventStat.VBCMin, '0') AS [VBC Minimum (RMS volts)], FORMAT(EventStat.VCAMin, '0') AS [VCA Minimum (RMS volts)], FORMAT(EventStat.IPeak, '0') AS [Current Peak (Amps)], FORMAT(EventStat.IAMax, '0') AS [IA Maximum (RMS Amps)], FORMAT(EventStat.IBMax, '0') AS [IB Maximum (RMS Amps)], FORMAT(EventStat.ICMax, '0') AS [IC Maximum (RMS Amps)], FORMAT(EventStat.IA2t, '0') AS [IA I2t (A2s)], FORMAT(EventStat.IB2t, '0') AS [IB I2t (A2s)], FORMAT(EventStat.IC2t, '0') AS [IC I2t (A2s)], VAN.Mapping AS [VAN Channel], VBN.Mapping AS [VBN Channel], VCN.Mapping AS [VCN Channel], IAN.Mapping AS [IAN Channel], IBN.Mapping AS [IBN Channel], ICN.Mapping AS [ICN Channel], IR.Mapping AS [IR Channel], FORMAT(RP.Imax1, '0.000') AS [Lmax 1], FORMAT(RP.Imax2, '0.000') AS [Lmax 2], FORMAT(RP.TripInitiate,'HH:mm:ss.fff') AS [Trip Initiation], (RP.TripTime / 10) AS [Trip Time (microsec)], (RP.PickupTime / 10) AS [Pickup Time (microsec)], FORMAT(RP.TripCoilCondition, '0.000') AS [Trip Coil Condition (Aps)] FROM Event JOIN MeterLine ON Event.MeterID = MeterLine.MeterID AND Event.LineID = MeterLine.LineID JOIN Meter ON Event.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN Line ON Event.LineID = Line.ID JOIN EventType ON Event.EventTypeID = EventType.ID LEFT OUTER JOIN FaultSummary ON Event.ID = FaultSummary.EventID AND FaultSummary.IsSelectedAlgorithm <> 0 AND FaultSummary.FaultNumber = 1 LEFT OUTER JOIN EventStat ON Event.ID = EventStat.EventID LEFT OUTER JOIN ChannelDetail VAN ON Event.MeterID = VAN.MeterID AND Event.LineID = VAN.LineID AND VAN.MeasurementType = 'Voltage' AND VAN.Phase = 'AN' AND VAN.MeasurementCharacteristic = 'Instantaneous' AND VAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN ChannelDetail VBN ON Event.MeterID = VBN.MeterID AND Event.LineID = VBN.LineID AND VBN.MeasurementType = 'Voltage' AND VBN.Phase = 'BN' AND VBN.MeasurementCharacteristic = 'Instantaneous' AND VBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN ChannelDetail VCN ON Event.MeterID = VCN.MeterID AND Event.LineID = VCN.LineID AND VCN.MeasurementType = 'Voltage' AND VCN.Phase = 'CN' AND VCN.MeasurementCharacteristic = 'Instantaneous' AND VCN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN ChannelDetail IAN ON Event.MeterID = IAN.MeterID AND Event.LineID = IAN.LineID AND IAN.MeasurementType = 'Current' AND IAN.Phase = 'AN' AND IAN.MeasurementCharacteristic = 'Instantaneous' AND IAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN ChannelDetail IBN ON Event.MeterID = IBN.MeterID AND Event.LineID = IBN.LineID AND IBN.MeasurementType = 'Current' AND IBN.Phase = 'BN' AND IBN.MeasurementCharacteristic = 'Instantaneous' AND IBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN ChannelDetail ICN ON Event.MeterID = ICN.MeterID AND Event.LineID = ICN.LineID AND ICN.MeasurementType = 'Current' AND ICN.Phase = 'CN' AND ICN.MeasurementCharacteristic = 'Instantaneous' AND ICN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN ChannelDetail IR ON Event.MeterID = IR.MeterID AND Event.LineID = IR.LineID AND IR.MeasurementType = 'Current' AND IR.Phase = 'RES' AND IR.MeasurementCharacteristic = 'Instantaneous' AND IR.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN RelayPerformance RP ON Event.ID = RP.EventID AND RP.ChannelID IN ( SELECT ID fROM ChannelDetail RPD WHERE Event.MeterID = RPD.MeterID AND Event.LineID = RPD.LineID ) CROSS JOIN ( SELECT COALESCE(CONVERT(FLOAT, ( SELECT TOP 1 Value FROM Setting WHERE Name = 'SystemFrequency' )), 60.0) AS Frequency ) System OUTER APPLY ( SELECT TOP 1 Disturbance.PerUnitMagnitude * 100 AS MagnitudePercent, Disturbance.Magnitude AS MagnitudeVolts FROM Disturbance JOIN EventType ON Disturbance.EventTypeID = EventType.ID AND EventType.Name = 'Sag' JOIN Phase ON Disturbance.PhaseID = Phase.ID AND Phase.Name = 'Worst' WHERE Disturbance.EventID = Event.ID AND Disturbance.StartTime <= dbo.AdjustDateTime2(FaultSummary.Inception, FaultSummary.DurationSeconds) AND Disturbance.EndTime >= FaultSummary.Inception ) Sag GO ALTER PROCEDURE [dbo].[selectBreakersForMeterIDByDateRange] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) DECLARE @dateStatement NVARCHAR(200) = N'CAST(TripCoilEnergized AS Date)' DECLARE @groupByStatement NVARCHAR(200) = N'CAST(TripCoilEnergized AS Date)' IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,TripCoilEnergized), @EventDateFrom)' SET @groupByStatement = N'DATEPART(HOUR, TripCoilEnergized), DateAdd(HOUR,DatePart(HOUR,TripCoilEnergized), @EventDateFrom)' END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,TripCoilEnergized), @EventDateFrom)' SET @groupByStatement = N'DATEPART(MINUTE, TripCoilEnergized), DateAdd(MINUTE,DatePart(MINUTE,TripCoilEnergized), @EventDateFrom)' END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,TripCoilEnergized), @EventDateFrom)' SET @groupByStatement = N'DATEPART(SECOND, TripCoilEnergized), DateAdd(SECOND,DatePart(SECOND,TripCoilEnergized), @EventDateFrom)' END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' create table #TEMP (Name varchar(max)) insert into #TEMP SELECT Name FROM (Select Distinct Name FROM BreakerOperationType) as t SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(max)), '') + '],' FROM #TEMP ORDER BY Name desc SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(max)), '') + '],' FROM #TEMP ORDER BY Name desc DROP TABLE #TEMP SET @SQLStatement = ' ' + ' SELECT * ' + ' INTO #selectedMeters ' + ' FROM String_To_Int_Table(@MeterID, '','') ' + ' ' + ' SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( ' + ' SELECT ' + @dateStatement + ' AS Date, ' + ' BreakerOperationType.Name, ' + ' COUNT(*) AS thecount ' + ' FROM BreakerOperation JOIN ' + ' BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN ' + ' Event ON Event.ID = BreakerOperation.EventID ' + ' WHERE MeterID IN (SELECT * FROM #selectedMeters) AND ' + ' TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate ' + ' GROUP BY ' + @groupByStatement + ', BreakerOperationType.Name ' + ') as table1 ' + ' PIVOT( ' + ' SUM(table1.thecount) ' + ' FOR table1.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ' + ' ) as pvt ' + ' ORDER BY Date ' exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DateTime ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectBreakersForMeterIDsByDate] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; declare @MeterIDs TABLE (ID int); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); DECLARE @counter INT = 0 DECLARE @eventDate DATE = CAST(@EventDateTo AS Date) DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate) SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate) CREATE TABLE #temp (thesiteid int, thesitename varchar(100)) INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter] SELECT thesiteid as siteid, thesitename as sitename , Normal as normal, Late as late, Indeterminate as indeterminate FROM ( SELECT #temp.thesiteid, #temp.thesitename , [BreakerOperationType].Name AS EventTypeName, COALESCE(EventCount, 0) AS EventCount FROM #temp CROSS JOIN [BreakerOperationType] LEFT OUTER JOIN ( SELECT MeterID, [BreakerOperationTypeID], COUNT(*) AS EventCount FROM [BreakerOperation] join [Event] on [BreakerOperation].[EventID] = [Event].[ID] where MeterID in (Select * from @MeterIDs) and (CAST([TripCoilEnergized] as Date) between @EventDateFrom and @EventDateTo) GROUP BY [BreakerOperationTypeID], MeterID ) AS E ON [BreakerOperationType].ID = E.[BreakerOperationTypeID] and E.MeterID = #temp.thesiteid ) AS EventDate PIVOT ( SUM(EventCount) FOR EventDate.EventTypeName IN (normal, late, indeterminate) ) as pvt ORDER BY sitename asc DROP TABLE #temp END GO ALTER PROCEDURE [dbo].[selectCompletenessForMeterIDByDateRange] @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) SELECT * INTO #selectedMeters FROM String_To_Int_Table(@MeterID, ',') SELECT Date as thedate, COALESCE(First, 0) AS '> 100%', COALESCE(Second, 0) AS '98% - 100%', COALESCE(Third, 0) AS '90% - 97%', COALESCE(Fourth, 0) AS '70% - 89%', COALESCE(Fifth, 0) AS '50% - 69%', COALESCE(Sixth, 0) AS '>0% - 49%' FROM ( SELECT Date, CompletenessLevel, COUNT(*) AS MeterCount FROM ( SELECT Date, CASE WHEN Completeness > 100.0 THEN 'First' WHEN 98.0 <= Completeness AND Completeness <= 100.0 THEN 'Second' WHEN 90.0 <= Completeness AND Completeness < 98.0 THEN 'Third' WHEN 70.0 <= Completeness AND Completeness < 90.0 THEN 'Fourth' WHEN 50.0 <= Completeness AND Completeness < 70.0 THEN 'Fifth' WHEN 0.0 < Completeness AND Completeness < 50.0 THEN 'Sixth' END AS CompletenessLevel FROM ( SELECT Date, 100.0 * CAST(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints AS FLOAT) / CAST(NULLIF(ExpectedPoints, 0) AS FLOAT) AS Completeness FROM MeterDataQualitySummary WHERE Date BETWEEN @startDate AND @endDate AND MeterID IN (SELECT * FROM #selectedMeters) ) MeterDataQualitySummary ) MeterDataQualitySummary GROUP BY Date, CompletenessLevel ) MeterDataQualitySummary PIVOT ( SUM(MeterDataQualitySummary.MeterCount) FOR MeterDataQualitySummary.CompletenessLevel IN (First, Second, Third, Fourth, Fifth, Sixth) ) as pvt ORDER BY Date END GO ALTER PROCEDURE [dbo].[selectCompletenessForMeterIDsByDate] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; declare @MeterIDs TABLE (ID int); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); DECLARE @counter INT = 0 DECLARE @eventDate DATE = CAST(@EventDateTo AS Date) DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate) SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate) CREATE TABLE #meters ( thesiteid int, thesitename varchar(100) ) INSERT INTO #meters Select [dbo].[Meter].[ID] as thesiteid, [dbo].[Meter].[Name] as thesitename from [dbo].[Meter] where Meter.ID in (Select * from @MeterIDs) DECLARE @thesiteid int DECLARE @thesitename varchar(100) CREATE TABLE #temp ( siteId int, siteName varchar(100), ExpectedPoints int, GoodPoints int, LatchedPoints int, UnreasonablePoints int, NoncongruentPoints int, DuplicatePoints int ) DECLARE db_cursor CURSOR FOR SELECT thesiteid, thesitename FROM #meters OPEN db_cursor FETCH NEXT FROM db_cursor INTO @thesiteid , @thesitename WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #temp SELECT @thesiteid, @thesitename, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[ExpectedPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as ExpectedPoints, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[GoodPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as GoodPoints, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[LatchedPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as LatchedPoints, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[UnreasonablePoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as UnreasonablePoints, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[NoncongruentPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as NoncongruentPoints, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[DuplicatePoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as DuplicatePoints FETCH NEXT FROM db_cursor INTO @thesiteid , @thesitename END CLOSE db_cursor DEALLOCATE db_cursor select * from #temp drop Table #temp drop Table #meters END GO ALTER PROCEDURE [dbo].[selectCorrectnessForMeterIDByDateRange] @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) SELECT * INTO #selectedMeters FROM String_To_Int_Table(@MeterID, ',') SELECT Date as thedate, COALESCE(First, 0) AS '> 100%', COALESCE(Second, 0) AS '98% - 100%', COALESCE(Third, 0) AS '90% - 97%', COALESCE(Fourth, 0) AS '70% - 89%', COALESCE(Fifth, 0) AS '50% - 69%', COALESCE(Sixth, 0) AS '>0% - 49%' FROM ( SELECT Date, CompletenessLevel, COUNT(*) AS MeterCount FROM ( SELECT Date, CASE WHEN Correctness > 100.0 THEN 'First' WHEN 98.0 <= Correctness AND Correctness <= 100.0 THEN 'Second' WHEN 90.0 <= Correctness AND Correctness < 98.0 THEN 'Third' WHEN 70.0 <= Correctness AND Correctness < 90.0 THEN 'Fourth' WHEN 50.0 <= Correctness AND Correctness < 70.0 THEN 'Fifth' WHEN 0.0 < Correctness AND Correctness < 50.0 THEN 'Sixth' END AS CompletenessLevel FROM ( SELECT Date, 100.0 * CAST(GoodPoints AS FLOAT) / CAST(NULLIF(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints, 0) AS FLOAT) AS Correctness FROM MeterDataQualitySummary WHERE Date BETWEEN @startDate AND @endDate AND MeterID IN (SELECT * FROM #selectedMeters) ) MeterDataQualitySummary ) MeterDataQualitySummary GROUP BY Date, CompletenessLevel ) MeterDataQualitySummary PIVOT ( SUM(MeterDataQualitySummary.MeterCount) FOR MeterDataQualitySummary.CompletenessLevel IN (First, Second, Third, Fourth, Fifth, Sixth) ) as pvt ORDER BY Date END GO ALTER PROCEDURE [dbo].[selectCorrectnessForMeterIDsByDate] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; declare @MeterIDs TABLE (ID int); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); DECLARE @counter INT = 0 DECLARE @eventDate DATE = CAST(@EventDateTo AS Date) DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate) SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate) CREATE TABLE #meters ( thesiteid int, thesitename varchar(100) ) INSERT INTO #meters Select [dbo].[Meter].[ID] as thesiteid, [dbo].[Meter].[Name] as thesitename from [dbo].[Meter] where Meter.ID in (Select * from @MeterIDs) DECLARE @thesiteid int DECLARE @thesitename varchar(100) CREATE TABLE #temp ( siteId int, siteName varchar(100), ExpectedPoints int, GoodPoints int, LatchedPoints int, UnreasonablePoints int, NoncongruentPoints int, DuplicatePoints int ) DECLARE db_cursor CURSOR FOR SELECT thesiteid, thesitename FROM #meters OPEN db_cursor FETCH NEXT FROM db_cursor INTO @thesiteid , @thesitename WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #temp SELECT @thesiteid, @thesitename, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[ExpectedPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as ExpectedPoints, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[GoodPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as GoodPoints, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[LatchedPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as LatchedPoints, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[UnreasonablePoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as UnreasonablePoints, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[NoncongruentPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as NoncongruentPoints, (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[DuplicatePoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as DuplicatePoints FETCH NEXT FROM db_cursor INTO @thesiteid , @thesitename END CLOSE db_cursor DEALLOCATE db_cursor select * from #temp drop Table #temp drop Table #meters END GO ALTER PROCEDURE [dbo].[selectDisturbancesDetailsByDate] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = CAST(@EventDateTo AS DATE) SELECT Meter.ID AS themeterid, Meter.Name AS thesite, MeterLocation.Latitude AS latitude, MeterLocation.Longitude AS longitude, COALESCE(Disturbance_Count, 0) AS Disturbance_Count, COALESCE([5], 0) AS [5], COALESCE([4], 0) AS [4], COALESCE([3], 0) AS [3], COALESCE([2], 0) AS [2], COALESCE([1], 0) AS [1], COALESCE([0], 0) AS [0] FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN ( SELECT MeterID, 6*[5] + 5*[4] + 4*[3] + 3*[2] + 2*[1] + 1*[0] AS Disturbance_Count, [5], [4], [3], [2], [1], [0] FROM ( SELECT Event.MeterID, DisturbanceSeverity.SeverityCode FROM DisturbanceSeverity JOIN Disturbance ON DisturbanceSeverity.DisturbanceID = Disturbance.ID JOIN Event ON Disturbance.EventID = Event.ID WHERE CAST(Disturbance.StartTime AS DATE) BETWEEN @startDate AND @endDate ) AS MeterSeverityCode PIVOT ( COUNT(SeverityCode) FOR SeverityCode IN ([5], [4], [3], [2], [1], [0]) ) AS PivotTable ) AS SeverityCount ON SeverityCount.MeterID = Meter.ID END GO ALTER PROCEDURE [dbo].[selectDisturbancesForMeterIDByDate] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; declare @MeterIDs TABLE (ID int); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); DECLARE @counter INT = 0 DECLARE @eventDate DATE = CAST(@EventDateTo AS Date) DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate) DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope') SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate) CREATE TABLE #temp (thesiteid int, thesitename varchar(100)) INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter] where Meter.ID in (Select * from @MeterIDs) SELECT thesiteid as siteid, thesitename as sitename, [5], [4], [3], [2], [1], [0] FROM ( SELECT #temp.thesiteid, #temp.thesitename, SeverityCodes.SeverityCode AS SeverityCode, COALESCE(DisturbanceCount, 0) AS DisturbanceCount FROM #temp Cross JOIN ( Select 5 as SeverityCode UNION SELECT 4 as SeverityCode UNION SELECT 3 as SeverityCode UNION SELECT 2 as SeverityCode UNION SELECT 1 as SeverityCode UNION SELECT 0 as SeverityCode ) AS SeverityCodes LEFT OUTER JOIN ( SELECT MeterID, SeverityCode, COUNT(*) AS DisturbanceCount FROM DisturbanceSeverity JOIN Disturbance ON Disturbance.ID = DisturbanceSeverity.DisturbanceID Join Event ON Event.ID = Disturbance.EventID JOIN VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID Where ( (CAST( Event.StartTime as Date) between @EventDateFrom and @EventDateTo)) and Disturbance.PhaseID = (SELECT ID FROM Phase WHERE Name = 'Worst') and VoltageEnvelope.Name = COALESCE(@voltageEnvelope, 'ITIC') GROUP BY SeverityCode, MeterID ) AS Disturbances ON #temp.thesiteid = Disturbances.MeterID AND Disturbances.SeverityCode = SeverityCodes.SeverityCode ) AS DisturbanceData PIVOT ( SUM(DisturbanceData.DisturbanceCount) FOR DisturbanceData.SeverityCode IN ([5], [4], [3], [2], [1], [0]) ) as pvt ORDER BY sitename asc DROP TABLE #temp END GO ALTER PROCEDURE [dbo].[selectDisturbancesForMeterIDByDateRange] @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) DECLARE @dateStatement NVARCHAR(200) = N'CAST(Disturbance.StartTime AS Date)' DECLARE @groupByStatement NVARCHAR(200) = N'CAST(Disturbance.StartTime AS Date)' IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,Disturbance.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(HOUR, Disturbance.StartTime), DateAdd(HOUR,DatePart(HOUR,Disturbance.StartTime), @EventDateFrom)' END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,Disturbance.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(MINUTE, Disturbance.StartTime), DateAdd(MINUTE,DatePart(MINUTE,Disturbance.StartTime), @EventDateFrom)' END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,Disturbance.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(SECOND, Disturbance.StartTime), DateAdd(SECOND,DatePart(SECOND,Disturbance.StartTime), @EventDateFrom)' END DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope') DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' create table #TEMP (Name varchar(max)) insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP WHERE Name != 0 ORDER BY Name desc SET @PivotColumns = @PivotColumns + '[0]' SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '0') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP WHERE Name != 0 ORDER BY Name desc SET @ReturnColumns = @ReturnColumns + 'COALESCE([0],0) as [0]' SET @SQLStatement = N' DECLARE @user varchar(max) = @username DECLARE @ids varchar(max) = @MeterID DECLARE @start DateTime = @startDate DECLARE @end DateTime = @endDate SELECT * INTO #selectedMeters FROM String_To_Int_Table(@ids, '','') SELECT DisturbanceDate as thedate, ' + @ReturnColumns + ' FROM ( SELECT ' + @dateStatement + ' AS DisturbanceDate, SeverityCode, COUNT(*) AS DisturbanceCount FROM DisturbanceSeverity JOIN Disturbance ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN Event ON Event.ID = Disturbance.EventID JOIN Phase ON Disturbance.PhaseID = Phase.ID JOIN VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID WHERE ( @MeterID = ''0'' OR MeterID IN (SELECT * FROM #selectedMeters) ) AND Phase.Name = ''Worst'' AND VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') AND Disturbance.StartTime BETWEEN @start AND @end AND Disturbance.StartTime <> @endDate GROUP BY ' + @groupByStatement + ', SeverityCode ) As DisturbanceDate PIVOT( SUM(DisturbanceDate.DisturbanceCount) FOR DisturbanceDate.SeverityCode IN(' + @PivotColumns + ') ) as pvt ORDER BY DisturbanceDate ' print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIMe, @endDate DATEtime, @EventDateFrom DATETIME, @voltageEnvelope VARCHAR(MAX)', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom, @voltageEnvelope = @voltageEnvelope END GO ALTER PROCEDURE [dbo].[selectEventsForCalendar] @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @counter INT = 0 DECLARE @eventDate DATE = (Select max(CAST(StartTime AS Date)) from Event) --'2015-03-23' DECLARE @numberOfDays INT = DATEDIFF ( day , (Select min(CAST(StartTime AS Date)) from Event), @eventDate) --365*5 SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate) CREATE TABLE #temp(Date DATE) WHILE (@counter <= @numberOfDays) BEGIN INSERT INTO #temp VALUES(@eventDate) SET @eventDate = DATEADD(DAY, 1, @eventDate) SET @counter = @counter + 1 END SELECT Date as thedate, Fault as faults, Interruption as interruptions, Sag as sags, Swell as swells, Other as others FROM ( SELECT #temp.Date, EventType.Name AS EventTypeName, COALESCE(EventCount, 0) AS EventCount FROM #temp CROSS JOIN EventType LEFT OUTER JOIN ( SELECT CAST(StartTime AS Date) AS EventDate, EventTypeID, COUNT(*) AS EventCount FROM Event GROUP BY CAST(StartTime AS Date), EventTypeID ) AS Event ON #temp.Date = Event.EventDate AND EventType.ID = Event.EventTypeID ) AS EventDate PIVOT ( SUM(EventCount) FOR EventDate.EventTypeName IN (Fault, Interruption, Sag, Swell, Other) ) as pvt ORDER BY Date DROP TABLE #temp END GO ALTER PROCEDURE [dbo].[selectEventsForMeterIDByDateRange] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) DECLARE @dateStatement NVARCHAR(200) = N'CAST(StartTime AS Date)' DECLARE @groupByStatement NVARCHAR(200) = N'CAST(StartTime AS Date)' IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(HOUR, StartTime), DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)' END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(MINUTE, StartTime), DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)' END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(SECOND, StartTime), DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)' END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + t.Name + '],' FROM (Select Name FROM EventType) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.Name + '], 0) AS [' + t.Name + '],' FROM (Select Name FROM EventType) AS t SET @SQLStatement = ' DECLARE @user varchar(max) = @username DECLARE @ids varchar(max) = @MeterID DECLARE @start DateTime = @startDate DECLARE @end DateTime = @endDate SELECT * INTO #selectedMeters FROM String_To_Int_Table(@ids, '','') SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( SELECT ' + @dateStatement + ' as Date, COUNT(*) AS EventCount, EventType.Name as Name FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID WHERE MeterID IN (SELECT * FROM #selectedMeters) AND StartTime >= @start AND StartTime < @end GROUP BY ' + @groupByStatement + ', EventType.Name ) as ed PIVOT( SUM(ed.EventCount) FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt ORDER BY Date ' --print @startDate --print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectEventsForMeterIDsByDate] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; declare @MeterIDs TABLE (ID int); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); DECLARE @counter INT = 0 DECLARE @eventDate DATE = CAST(@EventDateTo AS Date) DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate) SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate) CREATE TABLE #temp (thesiteid int, thesitename varchar(100)) INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter] where Meter.ID in (Select * from @MeterIDs) SELECT thesiteid as siteid, thesitename as sitename , Fault as faults, Interruption as interruptions, Sag as sags, Swell as swells, Other as others, Transient as transients FROM ( SELECT #temp.thesiteid, #temp.thesitename , EventType.Name AS EventTypeName, COALESCE(EventCount, 0) AS EventCount FROM #temp CROSS JOIN EventType LEFT OUTER JOIN ( SELECT MeterID, EventTypeID, COUNT(*) AS EventCount FROM Event WHERE (CAST([StartTime] as Date) between @EventDateFrom and @EventDateTo) GROUP BY EventTypeID, MeterID ) AS E ON EventType.ID = E.EventTypeID and E.MeterID = #temp.thesiteid ) AS EventDate PIVOT ( SUM(EventCount) FOR EventDate.EventTypeName IN (Fault, Interruption, Sag, Swell, Other, Transient) ) as pvt ORDER BY sitename asc DROP TABLE #temp END GO ALTER PROCEDURE [dbo].[selectFaultsForMeterIDByDateRange] @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) DECLARE @dateStatement NVARCHAR(200) = N'CAST(Event.StartTime AS Date)' DECLARE @groupByStatement NVARCHAR(200) = N'CAST(Event.StartTime AS Date)' IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,Event.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(HOUR, Event.StartTime), DateAdd(HOUR,DatePart(HOUR,Event.StartTime), @EventDateFrom)' END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,Event.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(MINUTE, Event.StartTime), DateAdd(MINUTE,DatePart(MINUTE,Event.StartTime), @EventDateFrom)' END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,Event.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(SECOND, Event.StartTime), DateAdd(SECOND,DatePart(SECOND,Event.StartTime), @EventDateFrom)' END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],' FROM (Select Distinct Line.VoltageKV FROM Line) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],' FROM (Select Distinct Line.VoltageKV FROM Line) AS t SET @SQLStatement = ' SELECT * ' + ' INTO #selectedMeters ' + ' FROM String_To_Int_Table(@MeterID, '','') ' + ' ' + ' SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( ' + ' SELECT ' + @dateStatement + ' AS Date, Line.VoltageKV, COUNT(*) AS thecount ' + ' FROM Event JOIN '+ ' EventType ON Event.EventTypeID = EventType.ID JOIN ' + ' Line ON Event.LineID = Line.ID ' + ' WHERE EventType.Name = ''Fault'' AND ' + ' MeterID IN (SELECT * FROM #selectedMeters) AND Event.StartTime >= @startDate AND Event.StartTime < @endDate ' + ' GROUP BY ' + @groupByStatement + ', EventType.Name, Line.VoltageKV ' + ' ) as eventtable ' + ' PIVOT( ' + ' SUM(eventtable.thecount) ' + ' FOR eventtable.VoltageKV IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ' + ' ) as pvt ' + ' ORDER BY Date ' exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DateTime ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectFaultsForMeterIDsByDate] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; declare @MeterIDs TABLE (ID int); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); DECLARE @counter INT = 0 DECLARE @eventDate DATE = CAST(@EventDateTo AS Date) DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate) SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate) CREATE TABLE #temp (thesiteid int, thesitename varchar(100)) INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter] where [dbo].[Meter].[ID] in (Select * from @MeterIDs) SELECT thesiteid as siteid, thesitename as sitename , Fault as faults FROM ( SELECT #temp.thesiteid, #temp.thesitename , EventType.Name AS EventTypeName, COALESCE(EventCount, 0) AS EventCount FROM #temp CROSS JOIN EventType LEFT OUTER JOIN ( SELECT MeterID, EventTypeID, COUNT(*) AS EventCount FROM Event where MeterID in (Select * from @MeterIDs) and (CAST([StartTime] as Date) between @EventDateFrom and @EventDateTo) GROUP BY EventTypeID, MeterID ) AS E ON EventType.ID = E.EventTypeID and E.MeterID = #temp.thesiteid and EventType.Name = 'Fault' ) AS EventDate PIVOT ( SUM(EventCount) FOR EventDate.EventTypeName IN (Fault, Interruption, Sag, Swell, Other) ) as pvt ORDER BY sitename asc DROP TABLE #temp END GO ALTER PROCEDURE [dbo].[selectExtensionsForMeterIDByDateRange] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) DECLARE @dateStatement NVARCHAR(200) = N'CAST(StartTime AS Date)' DECLARE @groupByStatement NVARCHAR(200) = N'CAST(StartTime AS Date)' IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(HOUR, StartTime), DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)' END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(MINUTE, StartTime), DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)' END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(SECOND, StartTime), DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)' END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @MiddleStatment NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t SELECT @MiddleStatment = @MiddleStatment + ' SELECT '+@dateStatement+' as date, '''+ t.ServiceName + ''' as ServiceName, Count(*) as EventCount FROM #temp WHERE dbo.' + t.HasResultFunction + '(ID) != '''' GROUP BY '+ @dateStatement + ' UNION' FROM (Select * FROM EASExtension) AS t SELECT * INTO #temp FROM EVENT WHERE StartTime Between @startDate AND @endDate AND MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID, ',')) CREATE INDEX tempIndex ON #temp (MeterID) SET @SQLStatement = ' SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( SELECT * FROM ( ' + SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION')) + ' ) as innertable ) as ed PIVOT( SUM(ed.EventCount) FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt ORDER BY Date DROP TABLE #temp ' --print @startDate --print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectHeatmapMeterLocationsTrending] -- Add the parameters for the stored procedure here @EventDate DateTime2, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; declare @MeterIDs TABLE (ID int); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); DECLARE @ChannelList VARCHAR(MAX) --SELECT @ChannelList = CAST([dbo].[Channel].[ID] as Varchar(Max)) + ',' + @ChannelList SELECT @ChannelList = Coalesce(@ChannelList+ ',','') + CAST([dbo].[Channel].[ID] as Varchar(Max)) FROM [dbo].[Channel] join [dbo].[Meter] on [dbo].[Meter].[ID] = [dbo].[Channel].[MeterID] and [dbo].[Meter].[ID] in (Select ID from @MeterIDs) where [dbo].[Channel].[MeasurementCharacteristicID] = ( SELECT [ID] FROM [dbo].[MeasurementCharacteristic] where Name = 'TotalTHD' ) and [dbo].[Channel].[PhaseID] = ( SELECT [ID] FROM [dbo].[Phase] where Name = 'AN' ) --Print @ChannelList BEGIN WITH TrendingDataPoint AS ( SELECT ChannelID, SeriesType, Time, Value FROM GetTrendingData(@EventDate, DATEADD(NANOSECOND, -100, DATEADD(minute, 5, @EventDate)), @ChannelList, default) AS TrendingData JOIN ( SELECT 0 AS ID, 'Minimum' AS SeriesType UNION SELECT 1 AS ID, 'Maximum' AS SeriesType UNION SELECT 2 AS ID, 'Average' AS SeriesType ) AS Series ON TrendingData.SeriesID = Series.ID ), TrendingData AS ( SELECT ChannelID, Time, Maximum, Minimum, Average FROM TrendingDataPoint PIVOT ( SUM(Value) FOR SeriesType IN (Maximum, Minimum, Average) ) AS TrendingData ) SELECT Coalesce([dbo].[Meter].[ID],0) as MeterID, [dbo].[MeterLocation].[Longitude] as Longitude, [dbo].[MeterLocation].[Latitude] as Latitude, [TrendingData].[Time] as thedate, Cast(Round(([TrendingData].[Maximum] * 100),0) as Int) as Value FROM [TrendingData] left outer join [dbo].[AlarmRangeLimit] on [dbo].[AlarmRangeLimit].[ChannelID] = [TrendingData].[ChannelID] left outer join [dbo].[HourOfWeekLimit] on [dbo].[HourOfWeekLimit].[ChannelID] = [TrendingData].[ChannelID] join [dbo].[Channel] on [dbo].[Channel].[ID] = TrendingData.ChannelID join [dbo].[Meter] on [dbo].[Meter].[ID] = [dbo].[Channel].[MeterID] join [dbo].[Meterlocation] on [dbo].[Meter].[MeterLocationID] = [dbo].[MeterLocation].[ID] where ([dbo].[HourOfWeekLimit].[HourOfWeek] = 0 or [dbo].[HourOfWeekLimit].[HourOfWeek] is null) order by [TrendingData].[Time] END END GO ALTER PROCEDURE [dbo].[selectMeterIDsForArea] -- Add the parameters for the stored procedure here @ax as float, @ay as float, @bx as float, @by as float, @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; SELECT distinct [dbo].[Meter].[ID] as TheMeterID from [dbo].[Meter] inner join [dbo].[MeterLocation] on [dbo].[Meter].[MeterLocationID] = [dbo].[MeterLocation].[ID] where ( [dbo].[MeterLocation].[Latitude] between @by and @ay and [dbo].[MeterLocation].[Longitude] between @ax and @bx ) END GO ALTER PROCEDURE [dbo].[selectMeterLocationsBreakers] @EventDateFrom DATETIME, @EventDateTo DATETIME, @meterIds AS varchar(max), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @CountColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' create table #TEMP (Name varchar(max)) insert into #TEMP SELECT Name FROM (Select Distinct Name FROM BreakerOperationType) as t SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(max)), '') + '],' FROM #TEMP ORDER BY Name desc SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(Name as varchar(20)), '') + '], 0) + ' FROM #TEMP ORDER BY Name desc SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(max)), '') + '],' FROM #TEMP ORDER BY Name desc DROP TABLE #TEMP SET @SQLStatement = ' SELECT * INTO #selectedMeters FROM String_To_Int_Table(@MeterIds, '','') SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN ( SELECT MeterID, COUNT(*) AS EventCount, BreakerOperationType.Name as OperationType FROM BreakerOperation JOIN Event ON BreakerOperation.EventID = Event.ID JOIN EventType ON Event.EventTypeID = EventType.ID JOIN BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID WHERE TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate GROUP BY Event.MeterID, BreakerOperationType.Name ) as ed PIVOT( SUM(ed.EventCount) FOR ed.OperationType IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt On pvt.MeterID = meter.ID WHERE Meter.ID IN (SELECT * FROM #selectedMeters) ORDER BY Meter.Name' print @SQLStatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectMeterLocationsDisturbances] @EventDateFrom as DateTime, @EventDateTo as DateTime, @meterIds AS varchar(max), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @CountColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' create table #TEMP (Name varchar(max)) insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP WHERE Name != 0 ORDER BY Name desc SET @PivotColumns = @PivotColumns + '[0]' SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) + ' FROM #TEMP WHERE Name != 0 ORDER BY Name desc SET @CountColumns = @CountColumns + 'COALESCE([0], 0) ' SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP WHERE Name != 0ORDER BY Name desc SET @ReturnColumns = @ReturnColumns + 'COALESCE([0], 0) as [0]' DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope') DROP TABLE #TEMP SET @SQLStatement = N' DECLARE @user varchar(max) = @username DECLARE @ids varchar(max) = @MeterIds DECLARE @start DateTime = @startDate DECLARE @end DateTime = @endDate SELECT * INTO #selectedMeters FROM String_To_Int_Table(@ids, '','') SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, ' + @CountColumns +' as Count, ' + @ReturnColumns + ' FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN ( SELECT MeterID, COUNT(*) AS EventCount, SeverityCode FROM Event JOIN Disturbance ON Event.ID = Disturbance.EventID JOIN Phase ON Phase.ID = Disturbance.PhaseID LEFT JOIN DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID WHERE Phase.Name = ''Worst'' AND Disturbance.StartTime >= @start AND Disturbance.StartTime < @end AND VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') GROUP BY Event.MeterID, SeverityCode ) as ed PIVOT( SUM(ed.EventCount) FOR ed.SeverityCode IN(' + @PivotColumns + ') ) as pvt On pvt.MeterID = meter.ID WHERE Meter.ID IN (SELECT * FROM #selectedMeters) Order By Name ' print @SqlStatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME , @voltageEnvelope VARCHAR(MAX)', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @voltageEnvelope = @voltageEnvelope END GO ALTER PROCEDURE [dbo].[selectMeterLocationsEvents] @EventDateFrom DATETIME, @EventDateTo DATETIME, @meterIds AS varchar(max), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @CountColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + t.Name + '],' FROM (Select Name FROM EventType) AS t SELECT @CountColumns = @CountColumns + 'COALESCE([' + t.Name + '], 0) + ' FROM (Select Name FROM EventType) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.Name + '], 0) AS [' + t.Name + '],' FROM (Select Name FROM EventType) AS t SET @SQLStatement = ' DECLARE @user varchar(max) = @username DECLARE @ids varchar(max) = @MeterIds DECLARE @start DateTime = @startDate DECLARE @end DateTime = @endDate SELECT * INTO #selectedMeters FROM String_To_Int_Table(@ids, '','') SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN ( SELECT MeterID, COUNT(*) AS EventCount, EventType.Name as Name FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID WHERE StartTime >= @start AND StartTime < @end GROUP BY Event.MeterID, EventType.Name ) as ed PIVOT( SUM(ed.EventCount) FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt On pvt.MeterID = meter.ID WHERE Meter.ID IN (SELECT * FROM #selectedMeters) ORDER BY Meter.Name' --print @startDate --print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate END GO ALTER PROCEDURE [dbo].[selectMeterLocationsFaults] @EventDateFrom DateTime, @EventDateTo DateTime, @meterIds AS varchar(max), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @CountColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],' FROM (Select Distinct Line.VoltageKV FROM Line) AS t SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) + ' FROM (Select Distinct Line.VoltageKV FROM Line) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],' FROM (Select Distinct Line.VoltageKV FROM Line) AS t SET @SQLStatement = 'SELECT * INTO #selectedMeters FROM String_To_Int_Table(@MeterIds, '','') SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN ( SELECT MeterID, COUNT(*) AS EventCount, VoltageKV FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Line ON Event.LineID = Line.ID WHERE StartTime >= @startDate AND StartTime < @endDate AND EventType.Name = ''Fault'' GROUP BY Event.MeterID, VoltageKV ) as ed PIVOT( SUM(ed.EventCount) FOR ed.VoltageKV IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt On pvt.MeterID = meter.ID WHERE Meter.ID IN (SELECT * FROM #selectedMeters) ORDER BY Meter.Name' print @SQLStatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectMeterLocationsExtensions] @EventDateFrom DATETIME, @EventDateTo DATETIME, @meterIds AS varchar(max), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @CountColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' DECLARE @MiddleStatment NVARCHAR(MAX) = N'' SELECT * INTO #temp FROM EVENT WHERE StartTime Between @startDate AND @endDate AND MeterID IN (SELECT * FROM String_To_Int_Table( @meterIds, ',')) CREATE INDEX tempIndex ON #temp (MeterID) CREATE TABLE #easTable( MeterID int, ServiceName varchar(max), EventCount int); SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t SELECT @CountColumns = @CountColumns + 'COALESCE([' + t.ServiceName + '], 0) + ' FROM (Select ServiceName FROM EASExtension) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t DECLARE @serviceName as varchar(max); DECLARE @hasResultFunction as varchar(max); DECLARE aCursor CURSOR FOR SELECT ServiceName, HasResultFunction FROM EASExtension OPEN aCursor; FETCH NEXT FROM aCursor into @serviceName, @hasResultFunction; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @Sql nvarchar(max) = N' INSERT INTO #easTable SELECT MeterID, '''+ @serviceName + ''' as ServiceName, Count(*) as EventCount FROM #temp WHERE dbo.' + @hasResultFunction + '(ID) != '''' GROUP BY MeterID' exec sp_executesql @sql FETCH NEXT FROM aCursor into @serviceName, @hasResultFunction; END; CLOSE aCursor; DEALLOCATE aCursor; SELECT @MiddleStatment = @MiddleStatment + ' SELECT MeterID, '''+ t.ServiceName + ''' as ServiceName, Count(*) as EventCount FROM #temp WHERE dbo.' + t.HasResultFunction + '(ID) != '''' GROUP BY MeterID UNION' FROM (Select * FROM EASExtension) AS t SET @SQLStatement = 'SELECT * INTO #selectedMeters FROM String_To_Int_Table(@MeterIds, '','') SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN #easTable as ed PIVOT( SUM(ed.EventCount) FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt On pvt.MeterID = meter.ID WHERE Meter.ID IN (SELECT * FROM #selectedMeters) ORDER BY Meter.Name DROP TABLE #temp DROP TABLE #easTable ' --print @startDate --print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectMeters] @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; SELECT distinct [dbo].[Meter].[ID] as id, [dbo].[Meter].[Name] from [dbo].[Meter] order by [dbo].[Meter].[Name] END GO ALTER PROCEDURE [dbo].[selectPowerLineClasses] @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; select distinct VoltageKV as class from [dbo].[Line] join Channel on Line.ID = Channel.LineID join Meter on Channel.MeterID = Meter.ID order by VoltageKV Desc END GO ALTER PROCEDURE [dbo].[selectSitesBreakersDetailsByDate] @EventDate AS DATETIME, @MeterID AS NVARCHAR(MAX), @username AS NVARCHAR(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DateTime DECLARE @endDate DateTime IF @context = 'day' BEGIN SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0) SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0) SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate) SET @endDate = DATEADD(SECOND, 1, @startDate) END SELECT Meter.ID AS meterid, Event.ID AS theeventid, EventType.Name AS eventtype, BreakerOperation.ID AS breakeroperationid, CAST(CAST(BreakerOperation.TripCoilEnergized AS TIME) AS NVARCHAR(100)) AS energized, BreakerOperation.BreakerNumber AS breakernumber, MeterLine.LineName AS linename, Phase.Name AS phasename, CAST(BreakerOperation.BreakerTiming AS DECIMAL(16,5)) AS timing, CAST(BreakerOperation.StatusTiming AS DECIMAL(16,5)) AS statustiming, BreakerOperation.BreakerSpeed AS speed, BreakerOperation.StatusBitChatter AS chatter, BreakerOperation.DcOffsetDetected AS dcoffset, BreakerOperationType.Name AS operationtype, (SELECT COUNT(*) FROM EventNote WHERE EventNote.EventID = Event.ID) as notecount FROM BreakerOperation JOIN Event ON BreakerOperation.EventID = Event.ID JOIN EventType ON EventType.ID = Event.EventTypeID JOIN Meter ON Meter.ID = Event.MeterID JOIN Line ON Line.ID = Event.LineID JOIN MeterLine ON MeterLine.LineID = Event.LineID AND MeterLine.MeterID = Meter.ID JOIN BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN Phase ON BreakerOperation.PhaseID = Phase.ID WHERE TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate AND Meter.ID IN (SELECT * FROM dbo.String_to_int_table(@MeterID, ',')) END GO ALTER PROCEDURE [dbo].[selectSitesCompletenessDetailsByDate] -- Add the parameters for the stored procedure here @EventDate AS DATETIME, @MeterID AS NVARCHAR(MAX), @username AS NVARCHAR(4000) AS BEGIN SET NOCOUNT ON; DECLARE @thedate DATE = CAST(@EventDate AS DATE) DECLARE @MeterIDs TABLE (ID INT); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',') DECLARE @TempTable TABLE (themeterid INT, thesite VARCHAR(100), thecount FLOAT, thename VARCHAR(100)); INSERT INTO @TempTable (themeterid, thesite , thecount , thename) SELECT Meter.ID AS meterid, Meter.Name AS thesite, ( SELECT COALESCE(( SELECT CAST(CAST((GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints) AS FLOAT) / NULLIF(CAST(expectedPoints AS FLOAT), 0) AS FLOAT) AS completenessPercentage FROM MeterDataQualitySummary WHERE MeterID = Meter.ID AND [Date] = @thedate ) , 0)) AS thecount, 'Completeness' as thename FROM MeterDataQualitySummary JOIN Meter ON Meter.ID = MeterDataQualitySummary.MeterID WHERE MeterID IN (SELECT * FROM @MeterIDs) AND CAST([Date] AS DATE) = @thedate INSERT INTO @TempTable (themeterid, thesite , thecount , thename) SELECT Meter.ID AS meterid, Meter.Name AS thesite, ( SELECT COALESCE(( SELECT CAST(NULLIF(CAST(expectedPoints AS FLOAT), 0) AS FLOAT) AS completenessPercentage FROM MeterDataQualitySummary WHERE MeterID = Meter.ID AND [Date] = @thedate ) , 0)) AS thecount, 'Expected' AS thename FROM MeterDataQualitySummary JOIN Meter ON Meter.ID = MeterDataQualitySummary.MeterID WHERE MeterID IN (SELECT * FROM @MeterIDs) AND CAST([Date] AS DATE) = @thedate INSERT INTO @TempTable (themeterid, thesite , thecount , thename) SELECT Meter.ID AS meterid, Meter.Name AS thesite, ( SELECT COALESCE(( SELECT CAST(CAST((GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints + DuplicatePoints) AS FLOAT) / NULLIF(CAST(expectedPoints AS FLOAT), 0) AS FLOAT) AS completenessPercentage FROM MeterDataQualitySummary WHERE MeterID = Meter.ID AND [Date] = @theDate ) , 0)) AS thecount, 'Received' AS thename FROM MeterDataQualitySummary JOIN Meter ON Meter.ID = MeterDataQualitySummary.MeterID WHERE MeterID IN (SELECT * FROM @MeterIDs) AND CAST([Date] AS DATE) = @thedate INSERT INTO @TempTable (themeterid, thesite , thecount , thename) SELECT Meter.ID AS meterid, Meter.Name AS thesite, ( SELECT COALESCE(( SELECT CAST(CAST((DuplicatePoints) AS FLOAT) / NULLIF(CAST(expectedPoints AS FLOAT), 0) AS FLOAT) AS completenessPercentage FROM MeterDataQualitySummary WHERE MeterID = Meter.ID AND [Date] = @thedate ) , 0)) AS thecount, 'Duplicate' AS thename FROM MeterDataQualitySummary JOIN Meter ON Meter.ID = MeterDataQualitySummary.MeterID WHERE MeterID IN (SELECT * FROM @MeterIDs) AND CAST([Date] AS DATE) = @thedate DECLARE @composite TABLE (theeventid INT, themeterid INT, thesite VARCHAR(100), Expected FLOAT, Received FLOAT, Duplicate FLOAT, Completeness FLOAT); DECLARE @sitename VARCHAR(100) DECLARE @themeterid INT DECLARE @theeventid INT DECLARE site_cursor CURSOR FOR SELECT DISTINCT themeterid, thesite FROM @TempTable OPEN site_cursor FETCH NEXT FROM site_cursor INTO @themeterid , @sitename WHILE @@FETCH_STATUS = 0 BEGIN INSERT @composite VALUES( ( SELECT TOP 1 MeterDataQualitySummary.ID FROM MeterDataQualitySummary WHERE MeterDataQualitySummary.MeterID = @themeterid AND CAST([Date] as Date) = @thedate ), @themeterid, @sitename, (SELECT thecount * 100 FROM @TempTable WHERE thename = 'Expected' AND thesite = @sitename), (SELECT thecount * 100 FROM @TempTable WHERE thename = 'Received' AND thesite = @sitename), (SELECT thecount * 100 FROM @TempTable WHERE thename = 'Duplicate' AND thesite = @sitename), (SELECT thecount * 100 FROM @TempTable WHERE thename = 'Completeness' AND thesite = @sitename) ) FETCH NEXT FROM site_cursor INTO @themeterid , @sitename END CLOSE site_cursor; DEALLOCATE site_cursor; SELECT * FROM @composite END GO ALTER PROCEDURE [dbo].[selectSitesCorrectnessDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @thedate DATE = CAST(@EventDate AS DATE) DECLARE @MeterIDs TABLE (ID INT); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',') DECLARE @TempTable TABLE (themeterid INT, thesite VARCHAR(100), thecount FLOAT, thename VARCHAR(100)); INSERT INTO @TempTable (themeterid, thesite , thecount , thename) SELECT Meter.ID AS meterid, Meter.Name AS thesite, ( SELECT COALESCE(ROUND(CAST(SUM(LatchedPoints) AS FLOAT) / NULLIF(CAST(SUM(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints) AS FLOAT), 0) * 100 , 0), 0) AS correctnessPercentage FROM MeterDataQualitySummary WHERE MeterID = Meter.ID AND [Date] = @thedate ) AS thecount, 'Latched' as thename FROM MeterDataQualitySummary JOIN Meter ON Meter.ID = MeterDataQualitySummary.MeterID WHERE MeterID IN (SELECT * FROM @MeterIDs) AND CAST([Date] AS DATE) = @thedate INSERT INTO @TempTable (themeterid, thesite , thecount , thename) SELECT Meter.ID AS meterid, Meter.Name AS thesite, ( SELECT COALESCE(ROUND(CAST(SUM(UnreasonablePoints) AS FLOAT) / NULLIF(CAST(SUM(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints) AS FLOAT), 0) * 100 ,0), 0) AS correctnessPercentage FROM MeterDataQualitySummary WHERE MeterID = [dbo].[Meter].[ID] AND [Date] = @thedate ) AS thecount, 'Unreasonable' AS thename FROM MeterDataQualitySummary JOIN Meter ON Meter.ID = MeterDataQualitySummary.MeterID WHERE MeterID IN (SELECT * FROM @MeterIDs) AND CAST([Date] AS DATE) = @thedate INSERT INTO @TempTable(themeterid, thesite , thecount , thename) SELECT Meter.ID AS meterid, Meter.Name AS thesite, ( SELECT COALESCE(ROUND(CAST(SUM(NoncongruentPoints) AS FLOAT) / NULLIF(CAST(SUM(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints) AS FLOAT), 0) * 100 , 0), 0) AS correctnessPercentage FROM MeterDataQualitySummary WHERE MeterID = Meter.ID AND [Date] = @thedate ) AS thecount, 'Noncongruent' AS thename FROM MeterDataQualitySummary JOIN Meter ON Meter.ID = MeterDataQualitySummary.MeterID WHERE MeterID IN (SELECT * FROM @MeterIDs) AND CAST([Date] AS DATE) = @thedate DECLARE @composite TABLE (theeventid INT, themeterid INT, thesite VARCHAR(100), Latched FLOAT, Unreasonable FLOAT, Noncongruent FLOAT, Correctness FLOAT); DECLARE @sitename VARCHAR(100) DECLARE @themeterid INT DECLARE @theeventid INT DECLARE site_cursor CURSOR FOR SELECT DISTINCT themeterid, thesite FROM @TempTable OPEN site_cursor FETCH NEXT FROM site_cursor INTO @themeterid, @sitename WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @composite VALUES( ( SELECT TOP 1 MeterDataQualitySummary.ID FROM MeterDataQualitySummary WHERE MeterDataQualitySummary.MeterID = @themeterid AND CAST([Date] AS DATE) = @theDate ), @themeterid, @sitename, (SELECT thecount FROM @TempTable WHERE thename = 'Latched' AND thesite = @sitename), (SELECT thecount FROM @TempTable WHERE thename = 'Unreasonable' AND thesite = @sitename), (SELECT thecount FROM @TempTable WHERE thename = 'Noncongruent' AND thesite = @sitename), ( SELECT 100.0 * CAST(GoodPoints AS FLOAT) / CAST(NULLIF(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints, 0) AS FLOAT) AS Correctness FROM MeterDataQualitySummary WHERE CAST([Date] AS DATE) = @theDate AND MeterID = @themeterid ) ) FETCH NEXT FROM site_cursor INTO @themeterid , @sitename END CLOSE site_cursor; DEALLOCATE site_cursor; SELECT * FROM @composite END GO ALTER PROCEDURE [dbo].[selectSitesDisturbancesDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DateTime = @EventDate DECLARE @endDate DateTime if @context = 'day' SET @endDate = DATEADD(DAY, 1, @startDate); if @context = 'hour' SET @endDate = DATEADD(HOUR, 1, @startDate); if @context = 'minute' SET @endDate = DATEADD(MINUTE, 1, @startDate); if @context = 'second' SET @endDate = DATEADD(SECOND, 1, @startDate); DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' create table #TEMP (Name varchar(max)) insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP ORDER BY Name desc SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP ORDER BY Name desc DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope') SET @SQLStatement = 'SELECT * INTO #meterSelections FROM String_To_Int_Table(@MeterID, '','') SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = pvt.MeterID AND StartTime >= @startDate AND StartTime < @endDate) EventID, MeterID, Site, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( SELECT Event.MeterID, COUNT(*) AS EventCount, SeverityCode, Meter.Name as Site FROM Disturbance JOIN DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN Event ON Disturbance.EventID = Event.ID JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Meter ON Event.MeterID = Meter.ID JOIN Phase ON Phase.ID = Disturbance.PhaseID JOIN VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID WHERE Phase.Name = ''Worst'' AND MeterID IN (SELECT * FROM #meterSelections) AND VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') AND Event.StartTime >= @startDate AND Event.StartTime < @endDate GROUP BY Event.MeterID,Meter.Name,SeverityCode ) as ed PIVOT( SUM(ed.EventCount) FOR ed.SeverityCode IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt ORDER BY MeterID ' print @startDate print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @voltageEnvelope VARCHAR(MAX) ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @voltageEnvelope = @voltageEnvelope END GO ALTER PROCEDURE [dbo].[selectSitesEventsDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DateTime = @EventDate DECLARE @endDate DateTime if @context = 'day' SET @endDate = DATEADD(DAY, 1, @startDate); if @context = 'hour' SET @endDate = DATEADD(HOUR, 1, @startDate); if @context = 'minute' SET @endDate = DATEADD(MINUTE, 1, @startDate); if @context = 'second' SET @endDate = DATEADD(SECOND, 1, @startDate); DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.Name as varchar(max)), '') + '],' FROM (Select Name FROM EventType) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.Name as varchar(max)), '') + '],' FROM (Select Name FROM EventType) AS t SET @SQLStatement = 'SELECT * INTO #meterSelections FROM String_To_Int_Table(@MeterID, '','') SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = pvt.MeterID AND StartTime >= @startDate AND StartTime < @endDate) EventID, MeterID, Site, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( SELECT Event.MeterID, COUNT(*) AS EventCount, EventType.Name, Meter.Name as Site FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Meter ON Event.MeterID = Meter.ID WHERE MeterID IN (SELECT * FROM #meterSelections) AND StartTime >= @startDate AND StartTime < @endDate GROUP BY Event.MeterID,Meter.Name,EventType.Name ) as ed PIVOT( SUM(ed.EventCount) FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt ORDER BY MeterID ' print @startDate print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate END GO ALTER PROCEDURE [dbo].[selectSitesEventsDetailsByDateRange] @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = CAST(@EventDateTo AS DATE) DECLARE @MeterIDs TABLE (ID INT); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',') SELECT CAST(CAST(Event.StartTime AS DATETIME2(7)) AS VARCHAR(26)) AS starttime, CAST(CAST(Event.EndTime AS DATETIME2(7)) AS VARCHAR(26)) AS endtime, Event.ID AS eventid, Meter.ID AS meterid, Meter.Name AS thesite, EventType.Name AS thename, MeterLine.LineName AS linename FROM Event JOIN EventType ON EventType.ID = Event.EventTypeID JOIN Meter ON Meter.ID = Event.MeterID JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Event.LineID WHERE Meter.ID IN (SELECT * FROM @MeterIDs) AND CAST(StartTime AS DATE) BETWEEN @startDate AND @endDate ORDER BY StartTime END GO ALTER PROCEDURE [dbo].[selectSitesFaultsDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DateTime = @EventDate DECLARE @endDate DateTime if @context = 'day' SET @endDate = DATEADD(DAY, 1, @startDate); if @context = 'hour' SET @endDate = DATEADD(HOUR, 1, @startDate); if @context = 'minute' SET @endDate = DATEADD(MINUTE, 1, @startDate); if @context = 'second' SET @endDate = DATEADD(SECOND, 1, @startDate); SELECT * INTO #meterSelections FROM String_to_int_table(@MeterID, ',') ; WITH FaultDetail AS ( SELECT FaultSummary.ID AS thefaultid, Meter.Name AS thesite, Meter.ShortName AS theshortsite, MeterLocation.ShortName AS locationname, Meter.ID AS themeterid, Line.ID AS thelineid, Event.ID AS theeventid, MeterLine.LineName AS thelinename, Line.VoltageKV AS voltage, CAST(CAST(Event.StartTime AS TIME) AS NVARCHAR(100)) AS theinceptiontime, FaultSummary.FaultType AS thefaulttype, CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS NVARCHAR(19)) END AS thecurrentdistance, (SELECT COUNT(*) FROM FaultNote WHERE FaultSummary.ID = FaultNote.FaultSummaryID) as notecount, ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY FaultSummary.IsSuppressed, FaultSummary.IsSelectedAlgorithm DESC, FaultSummary.Inception) AS rk FROM FaultSummary JOIN Event ON FaultSummary.EventID = Event.ID JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Meter ON Event.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN Line ON Event.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID WHERE EventType.Name = 'Fault' AND Event.StartTime >= @startDate AND Event.StartTime < @endDate AND Meter.ID IN (SELECT * FROM #meterSelections) ) SELECT * FROM FaultDetail WHERE rk = 1 END GO ALTER PROCEDURE [dbo].[selectSitesTrendingDataDetailsByDate] -- Add the parameters for the stored procedure here @EventDate DateTime2, @colorScaleName VARCHAR(200), @MeterID AS nvarchar(MAX), @username AS nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @MeterIDs TABLE (ID int); DECLARE @ChannelID AS nvarchar(MAX); DECLARE @Date as DateTime2; SET @Date = CAST(@EventDate AS DATE) -- Create MeterIDs Table INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); -- Trending Data SELECT Meter.ID as meterid, Meter.Name as Name, Channel.ID as channelid, DailyTrendingSummary.Date as date, MIN(Minimum/COALESCE(Channel.PerUnitValue,1)) as Minimum, MAX(Maximum/COALESCE(Channel.PerUnitValue,1)) as Maximum, AVG(Average/COALESCE(Channel.PerUnitValue,1)) as Average, MeasurementCharacteristic.Name as characteristic, MeasurementType.Name as measurementtype, Phase.Name as phasename FROM DailyTrendingSummary JOIN Channel ON DailyTrendingSummary.ChannelID = Channel.ID JOIN Meter ON Meter.ID = Channel.MeterID JOIN MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN Phase ON Channel.PhaseID = Phase.ID WHERE Meter.ID IN (SELECT * FROM @MeterIDs) AND Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourColorScaleName = @colorScaleName) AND Date = @Date GROUP BY Date, Meter.ID, Meter.Name, MeasurementCharacteristic.Name, MeasurementType.Name, Phase.Name, Channel.ID ORDER BY Date END GO ALTER PROCEDURE [dbo].[selectSitesTrendingDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; declare @theDate as Date declare @MeterIDs TABLE (ID int); set @theDate = CAST(@EventDate as Date) INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); Select Meter.ID as meterid, Channel.ID as channelid, Meter.Name as sitename, [dbo].[AlarmType].[Name] as eventtype, [dbo].[MeasurementCharacteristic].[Name] as characteristic, [dbo].[MeasurementType].[Name] as measurementtype, [dbo].[Phase].[Name] as phasename, Channel.HarmonicGroup, SUM (ChannelAlarmSummary.AlarmPoints) as eventcount, @theDate as date from Channel join ChannelAlarmSummary on ChannelAlarmSummary.ChannelID = Channel.ID and Date = @theDate join Meter on Channel.MeterID = Meter.ID and [MeterID] in ( Select * from @MeterIDs) join [dbo].[AlarmType] on [dbo].[AlarmType].[ID] = ChannelAlarmSummary.AlarmTypeID and ([dbo].[AlarmType].[Name] = 'OffNormal' or [dbo].[AlarmType].[Name] = 'Alarm') join [dbo].[MeasurementCharacteristic] on Channel.MeasurementCharacteristicID = [dbo].[MeasurementCharacteristic].[ID] join [dbo].[MeasurementType] on Channel.MeasurementTypeID = [dbo].[MeasurementType].ID join [dbo].[Phase] on Channel.PhaseID = [dbo].[Phase].ID Group By Meter.ID , Channel.ID , Meter.Name , [dbo].[AlarmType].[Name], [MeasurementCharacteristic].[Name] , [MeasurementType].[Name] , [dbo].[Phase].[Name], Channel.HarmonicGroup Order By Meter.ID END GO ALTER PROCEDURE [dbo].[selectSitesExtensionsDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DateTime = @EventDate DECLARE @endDate DateTime if @context = 'day' SET @endDate = DATEADD(DAY, 1, @startDate); if @context = 'hour' SET @endDate = DATEADD(HOUR, 1, @startDate); if @context = 'minute' SET @endDate = DATEADD(MINUTE, 1, @startDate); if @context = 'second' SET @endDate = DATEADD(SECOND, 1, @startDate); DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' DECLARE @MiddleStatment NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t SELECT @MiddleStatment = @MiddleStatment + ' SELECT MeterID, '''+ t.ServiceName + ''' as ServiceName, Count(*) as EventCount FROM #temp WHERE dbo.' + t.HasResultFunction + '(ID) != '''' GROUP BY MeterID UNION' FROM (Select * FROM EASExtension) AS t SET @SQLStatement = 'SELECT * INTO #meterSelections FROM String_To_Int_Table(@MeterID, '','') SELECT * INTO #temp FROM EVENT WHERE StartTime Between @startDate AND @endDate AND MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID, '','')) SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = Meter.ID AND StartTime >= @startDate AND StartTime < @endDate) as EventID, Meter.ID as MeterID, Meter.Name as Site, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM Meter Join ( ' + SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION')) + ' ) as ed PIVOT( SUM(ed.EventCount) FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt ON pvt.MeterID = Meter.ID WHERE Meter.ID IN (SELECT * FROM #meterSelections) ORDER BY MeterID DROP Table #temp ' --print @startDate --print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate END GO ALTER PROCEDURE [dbo].[selectTrendingDataByChannelByDate] @StartDate DateTime2, @EndDate DateTime2, @colorScale AS varchar(200), @MeterID AS nvarchar(MAX), @username AS nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @MeterIDs TABLE (ID int); DECLARE @ChannelID AS nvarchar(MAX); DECLARE @BeginDate DateTime2; DECLARE @StopDate DateTime2; SET @BeginDate = @StartDate; SET @StopDate = @EndDate; -- Create MeterIDs Table INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); -- Trending Data SELECT Date, MIN(Minimum/COALESCE(Channel.PerUnitValue, 1)) as Minimum, MAX(Maximum/COALESCE(Channel.PerUnitValue,1)) as Maximum, AVG(Average/COALESCE(Channel.PerUnitValue,1)) as Average FROM DailyTrendingSummary JOIN Channel ON DailyTrendingSummary.ChannelID = Channel.ID JOIN Meter ON Meter.ID = Channel.MeterID WHERE Meter.ID IN (SELECT * FROM @MeterIDs) AND Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourChannel.ContourColorScaleName = @colorScale) AND Date >= @BeginDate AND Date <= @StopDate GROUP BY Date ORDER BY Date END GO ALTER PROCEDURE [dbo].[selectTrendingForCalendar] @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @counter INT = 0 DECLARE @eventDate DATE = (Select max(CAST(Time AS Date)) from AlarmLog) DECLARE @numberOfDays INT = DATEDIFF ( day , (Select min(CAST(Time AS Date)) from AlarmLog), @eventDate) SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate) CREATE TABLE #temp(Date DATE) WHILE (@counter <= @numberOfDays) BEGIN INSERT INTO #temp VALUES(@eventDate) SET @eventDate = DATEADD(DAY, 1, @eventDate) SET @counter = @counter + 1 END SELECT Date as thedate, Offnormal as offnormal, Alarm as alarm FROM ( SELECT #temp.Date, AlarmType.Name AS AlarmTypeName, COALESCE(AlarmCount, 0) AS AlarmCount FROM #temp CROSS JOIN AlarmType LEFT OUTER JOIN ( SELECT CAST(Time AS Date) AS AlarmDate, AlarmTypeID, COUNT(*) AS AlarmCount FROM AlarmLog join Channel on AlarmLog.ChannelID = Channel.ID GROUP BY CAST(Time AS Date), AlarmTypeID ) AS Alarm ON #temp.Date = Alarm.AlarmDate AND AlarmType.ID = Alarm.AlarmTypeID ) AS AlarmDate PIVOT ( SUM(AlarmCount) FOR AlarmDate.AlarmTypeName IN (Offnormal,Alarm) ) as pvt ORDER BY Date DROP TABLE #temp END GO ALTER PROCEDURE [dbo].[selectTrendingForMeterIDByDateRange] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) SELECT * INTO #selectedMeters FROM String_To_Int_Table(@MeterID, ',') SELECT AlarmDate as thedate, COALESCE(OffNormal,0) as Offnormal, COALESCE(Alarm,0) as Alarm FROM( SELECT Date AS AlarmDate, AlarmType.Name, SUM(AlarmPoints) as AlarmPoints FROM ChannelAlarmSummary JOIN Channel ON ChannelAlarmSummary.ChannelID = Channel.ID JOIN AlarmType ON AlarmType.ID = ChannelAlarmSummary.AlarmTypeID WHERE MeterID IN (SELECT * FROM #selectedMeters) AND Date >= @startDate AND Date < @endDate GROUP BY Date, AlarmType.Name ) AS table1 PIVOT( SUM(table1.AlarmPoints) FOR table1.Name IN(Alarm, OffNormal) ) as pvt END GO ALTER PROCEDURE [dbo].[selectTrendingForMeterIDsByDate] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; -- declare @tablecount int = (Select count(*) from AlarmLog); --if ( @tablecount = 0 ) --Begin --return; --End declare @MeterIDs TABLE (ID int); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); DECLARE @counter INT = 0 DECLARE @eventDate DATE = CAST(@EventDateTo AS Date) DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate) SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate) CREATE TABLE #temp (thesiteid int, thesitename varchar(100)) INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter] where [dbo].[Meter].[ID] in (Select * from @MeterIDs) SELECT thesiteid as siteid, thesitename as sitename , COALESCE(Offnormal,0) as offnormal, COALESCE(Alarm,0) as alarm FROM ( SELECT #temp.thesiteid, #temp.thesitename , AlarmType.Name AS AlarmTypeName, COALESCE(AlarmCount, 0) AS AlarmCount FROM #temp CROSS JOIN AlarmType LEFT OUTER JOIN ( SELECT MeterID, AlarmTypeID, Sum(AlarmPoints) AS AlarmCount FROM ChannelAlarmSummary join Channel on ChannelAlarmSummary.ChannelID = Channel.ID join Meter on Channel.MeterID = Meter.ID where MeterID in (Select * from @MeterIDs) and Date between @EventDateFrom and @EventDateTo GROUP BY AlarmTypeID, MeterID ) AS E ON AlarmType.ID = E.AlarmTypeID and E.MeterID = #temp.thesiteid ) AS AlarmDate PIVOT ( SUM(AlarmCount) FOR AlarmDate.AlarmTypeName IN ( Alarm, Offnormal) ) as pvt ORDER BY sitename asc DROP TABLE #temp END GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.MinFaultSegmentCycles', '1.0', '1.0') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FolderExclusion', '', '') GO INSERT INTO MeasurementType(Name, Description) VALUES ('TripCoilCurrent', 'Relay Trip Coil Energization Current') GO CREATE VIEW AssetGroupView AS SELECT AssetGroup.ID, AssetGroup.Name, COUNT(DISTINCT AssetGroupAssetGroup.ChildAssetGroupID) as AssetGroups, COUNT(DISTINCT MeterAssetGroup.MeterID) as Meters, COUNT(DISTINCT LineAssetGroup.LineID) as Lines, COUNT(DISTINCT UserAccountAssetGroup.UserAccountID) as Users FROM AssetGroup LEFT JOIN AssetGroupAssetGroup ON AssetGroup.ID = AssetGroupAssetGroup.ParentAssetGroupID LEFT JOIN MeterAssetGroup ON AssetGroup.ID = MeterAssetGroup.AssetGroupID LEFT JOIN LineAssetGroup ON AssetGroup.ID = LineAssetGroup.AssetGroupID LEFT JOIN UserAccountAssetGroup ON AssetGroup.ID = UserAccountAssetGroup.AssetGroupID GROUP BY AssetGroup.ID,AssetGroup.Name GO CREATE TABLE Breaker ( AssetKey VARCHAR(50) NULL, Speed FLOAT NULL ) GO CREATE TABLE EmailGroupAssetGroup ( EmailGroupID INT NULL, AssetGroupID INT NULL ) GO ALTER TABLE FileGroupLocalToRemote ADD RemoteXDAInstanceID INT NOT NULL REFERENCES RemoteXDAInstance(ID) GO ALTER TABLE BreakerOperationType ADD CONSTRAINT UQ_BreakerOperationType_Name UNIQUE(Name) GO ALTER TABLE EventType ADD CONSTRAINT UQ_EventType_Name UNIQUE(Name) GO ALTER TABLE SegmentType ADD CONSTRAINT UQ_SegmentType_Name UNIQUE(Name) GO ALTER TABLE WorkbenchFilter ALTER COLUMN Meters VARCHAR(MAX) NULL GO ALTER TABLE FaultSummary ADD ReactanceRatio FLOAT NOT NULL GO